To take a different approach, what is driving your read workload - Is it the application, or do you have a bunch of reports hanging off the system that are driving the load? Depending on the nature of your workload you might be able to re-shuffle your hardware or push some of it off onto other machines.
A couple of ideas:
If you have a lot of read workload driven by reports (as opposed to application screens), consider moving those off onto a replicated server (more than one if necessary). More extremely, if you have the option of doing this, you could create a data mart specificually tuned for the reporting. Obviously this might depend on how up to date the data has to be.
It should be possible to do this without having to interfere in the application code, so it's not as an extreme approach as one might think. If you need it to be up to date you could do something with change data capture at the expense of extra complexity.
If your DB server is I/O bound but you have plenty of spare CPU capacity (or maybe you can upgrade the CPUs) you might get some mileage from placing some or all of your database on SSDs.
Without looking into the technical specifics of your problem it's hard to see what can actually be done with the workload.
Even when a machine is fixed in a given timezone, storing timezone-dependent date/time information is not very safe. What do you do during Daylight Saving Time? You have the same problem - you skip ahead or repeat an hour. As the others suggested, you should always store your data using UTC, then it is easy to convert to any time zone you want, without having to know what time zone the server was in when the data was stored (though you can optionally store that information separately, or you can use DATETIMEOFFSET
- but I haven't found that very useful since it, also, doesn't account for DST).
For easy situations (e.g. no DST involved), you can write a simple helper function to convert UTC to whatever time zone is appropriate. Even better if you just know the offset (e.g. +5 hours or -4 hours). This is a simple DATEADD
operation.
For situations where DST is involved, I've always used a calendar table. It is very easy to populate a table with 30 years of data (one row for each day) and also store the offset, in minutes, for each day (given your time zone and whether the date falls in DST or not). Yes these can change for future dates, but the only time this can become tricky is if you're booking events farther into the future than the advance warning you get when the government decides to change the policy (as they did a few years ago). Converting UTC data on a given date to the time zone of your choice is still a simple DATEADD
operation, except now it involves looking up the actual difference for that date in a table.
For data you're entering now, you can simply use GETUTCDATE()
instead of GETDATE()
/CURRENT_TIMESTAMP
. This returns UTC time regardless of your time zone, and whether or not the machine is set up to observe DST.
Best Answer
You can check this only if you are monitoring this.
You can also check for problems at a certain time, if you see in SQL Server Log waiting to perform IO operation.
I would start by doing all the Glenn Alan Berry scripts. https://sqlserverperformance.wordpress.com/2016/06/08/sql-server-diagnostic-information-queries-for-june-2016/
You can run a perfmon together with a sqlprofiler and look for the statements that are causing problems (this will give you hardware an query information which you will have to combine)
What I usually get my customers to use is SQL Sentry(SentryOne) this is a montoring tool which has a month free trial. This will show all information you need correlated to queries in an easy to understand GUI. This however is not the only solution, but it is the one I prefer.