Wish i had this little gem back when working in the product support group years back. Still going to use it today to during system testing to assist in uncovering inefficiencies. Love the tips, Pinal!
Pinal, could you please also cover the "Why' part in your 'How' videos. Example: Why do you need to know most used DB file. This would really help those of us that are 'accidental dba's' Overall really like your channel. Quality stuff and good use of our time. Cheers. 🙂 P.S. Turns out i had to watch the video to the end to see the 'Why' part.
Here is scanario sir - there is an audit table which is in use by windows service cluster at frequency of milliseconds (by design). I had to set some housekeeping on it to clean data after 7 days in order to reduce IO when service cluster queries the table but I think partitioning on time based column might help to reduce the IO with querying particular partition. In my case windows service is always active on most recent Audit logs from the table. Does not touch old logs from audit for functioning but i still need old logs for the troubleshooting purpose. Also when it goes for housekeeping it slows down the service farm for a moment. Could partitioning be useful in such case ?
@@PinalDaveSQLAuthority thanks sir. Already utilising the index on this big table and it improved the query response at great extent but still the cpu consumption is high and constant on this one specific table as it gets queried at every 50-100 ms and dumping lot of records end of day. Restructuring service is one last option left where we don't rely on audit table for status tracking and health checks.
Thanks and good one! but how do we know what queries making them - particularly ? because so many queries may access that mdf file. do have any scripts for this ?
I have had a glance now and if you don't mind would you able to guide me that what is the key word to search that particular blog ? or Could you send that link- hope not asking you too much. Once again thanks.
I am a great fan of your work, I have a different question than the subject of the video I have a multitenant application, where each tenant has its own DB, how can I push script updates to all the tenant DBs efficiently?
Wish i had this little gem back when working in the product support group years back. Still going to use it today to during system testing to assist in uncovering inefficiencies.
Love the tips, Pinal!
Couldn't agree more!
Compliments of the season to you, Pinal.
Thank you for this one, it is a really useful script to have!!!
Thank you so much
Thanks for the video Pinal :)
My pleasure!
Pinal, could you please also cover the "Why' part in your 'How' videos.
Example: Why do you need to know most used DB file.
This would really help those of us that are 'accidental dba's'
Overall really like your channel. Quality stuff and good use of our time.
Cheers. 🙂
P.S. Turns out i had to watch the video to the end to see the 'Why' part.
Great to hear that.
Pinal sir aap video editing ke liye kaunsa software use karte ho ?
Camtasia
Very useful tip 👍. Can you please make one on partitioning - When/how to use it for lifting the performance.
Partitions do not help performance, they are data management feature.
Here is scanario sir - there is an audit table which is in use by windows service cluster at frequency of milliseconds (by design). I had to set some housekeeping on it to clean data after 7 days in order to reduce IO when service cluster queries the table but I think partitioning on time based column might help to reduce the IO with querying particular partition. In my case windows service is always active on most recent Audit logs from the table. Does not touch old logs from audit for functioning but i still need old logs for the troubleshooting purpose. Also when it goes for housekeeping it slows down the service farm for a moment. Could partitioning be useful in such case ?
@@chrgbhtt A good index can help reduce IO, Partition does not help reduce IO. It may rather increase it.
@@PinalDaveSQLAuthority thanks sir. Already utilising the index on this big table and it improved the query response at great extent but still the cpu consumption is high and constant on this one specific table as it gets queried at every 50-100 ms and dumping lot of records end of day. Restructuring service is one last option left where we don't rely on audit table for status tracking and health checks.
Thanks and good one! but how do we know what queries making them - particularly ? because so many queries may access that mdf file. do have any scripts for this ?
Queries generating most write io usually so it. Check on sqlauthority blog.
@@PinalDaveSQLAuthority will do. Thanks ! Appreciated
I have had a glance now and if you don't mind would you able to guide me that what is
the key word to search that particular blog ? or Could you send that link- hope not asking you too much. Once again thanks.
I am a great fan of your work, I have a different question than the subject of the video
I have a multitenant application, where each tenant has its own DB, how can I push script updates to all the tenant DBs efficiently?
You will have to come up with solution with powershell
Hi sir, I have done some mistakes in update query, total table data has been updated, how to undo the old table.
Just restore from Point in Time. I have created a video on this topic earlier on this channel.
Namaskaram, pinal dave garu, how do we know which file is slow from the result set
That you can try to guess based on the IO Latency and I will create another video for it.
how many years stored data can be saved or need archived?
One can't say this. Need to understand business data more.
Is there a way to remove read bytes caused by backup ? Thanks !
Currently I do not know the situation.