Post reply

Scripts for monitoring

  • September 6, 2011 at 10:11 am

    #1377549

    There are server variables that can show you total runtime data on CPU, IO, or such (@@idle for example). This is stored in one lump sum as in, total since the server was last restarted. I used TempDB create date to find out when the server was last restarted in case the server was restarted after data was already pushed into the server stats tables.

    I take the proc and use a SQL Agent job that is run every so often to take the number that is now stored to those variables, and subtract what has already been seen during that timeline provided by the schedule of the SQL agent job.

    Therefore, it first records total amounts stored in those variables. It then does a difference of what the first value recorded from the last restart summed to all other values since then and what has not yet been stored and that is the current value for the new recording.

    so, the initial value for the variable may be 1111111111111 but the second time the value is captured it is 1111111111112 so the first recorded value is 1111111111111, the second record value is 1 the next time the variable is seen it gets changed to 1111111111113 so the value saved again is 1 which is the difference of summed recorded values. So, when you see 100000 instead of 1 for that time frame you know that resources were being consumed at a higher rate.

    It's a temp thing as over time this can be a little intensive with all the summing. The excel spreadsheet would then have the values you want back in ranges.

    Overall, it selects the values from system variables, checks to see what they were last, records the difference in value. You can then see which times had a large change in those values and start to try and figure out why.

    Small solution without using perfmon, profiler or something with a lot more detail.

  • September 9, 2011 at 1:56 am

    #1379053

    Hi,

    for a quick performance check i use the open-source SQL Live Monitor from microsoft codeplex: http://sqlmonitor.codeplex.com/ .

    It's able to connect to the different sql versions by remote or local.

    For sure there are better solutions delivered from microsoft or others but that one is free an portable.

    Best Regards

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply