2

I actually built some "Custom" Monitoring tool to track information from the DMV Tables and store them in some kind of DataWarehouse I'm taking snapshots each 5minutes.

the data I'm retrieving is:

Database:

  • File Latency (ldf and mdf)
  • File Sizes and use
  • Transactions
  • Log Growths and Flushes
  • Database CPU used
  • Database Buffer MB used

SERVER:

  • SQL CPU / IDLE PROCESS / OTHER PROCESSES
  • RAM (TARGET/USED)
  • Page Life Expectancy
  • Cache Hit Ratio
  • User Connections / Database Connections

LOCKWAITS:

  • General Locks/DeadLocks/Waits information (Network... Io Latch...)

QUERIES:

In this part, I'm a bit lost... I would like to be able to detect problematic queries on my server and to do so I need to store historical information as the information on some of the DMVs is cumulative so if I want to some averages like for example execution_count I need to snapshot them. I don't know what Key KPIS and threshold should a query reach to be considered a problematic query...

Also, I don't know if I should monitor more KPIs in order to detect CPU pressure/ RAM pressure / Disk pressure.

Any help on improving it will be appreciated!

  • Just set you expected threshold, by considering the RAM, CPU and IOPS of your disk. e.g. if you are using SSD that 20,000 IOPS, and you expect 10,000 row return from 10 millions row, then the expected threshold should not exceed 10 seconds. This allow you to detect badly written query or make decision to create more index, etc. – mootmoot Sep 2 '16 at 9:06
  • Hello Moot, dumb question but, how do you make the relation between this counters to end with a 10 seconds max threshold? – J1mmy Sep 2 '16 at 10:14
  • This is just a suggestion. To be more precise, you need to create a simple table with index and filled up with simple value, then do a select to check it out. If the simple table return 10k row from index took 10 seconds, you should expect the live data will take longer than that, but should not be way off (say 10 times slower). Disk I/O is the biggest DBMS physical bottleneck and basic threshold. – mootmoot Sep 2 '16 at 11:25
  • 1
    Are you using a monitoring solution like IderaDM or SQL Sentry? Just throwing this out there that the development hours / money you are spending trying to build this homegrown solution could be spent purchasing a product and you can, ya know, go back to doing DBA stuff :) – Kris Gruttemeyer Sep 2 '16 at 13:15
  • I could do what you said, but this is being developed in my "free" time in the office as I don't have too much projects to administer at the moment, my idea is to create some kind of "DataWarehouse" where we will store information of performance of different servers for different applications (BO, MSTR, DB2) some information will be application specific of course, but high level server performance is the same on everyone. The point here is to give Key users a reporting tool where they can see the performance of their database and say, "hey" they are charging me 60% of costs, but its true! – J1mmy Sep 2 '16 at 13:36
3

If you don't know then you're not ready to write a tool of this nature. To echo the comments of others this is why people buy a tool to do it better than we can, and the prices are super reasonable for very small shops of a few servers.

The only people writing decent tools for themselves are highly skilled professionals sometimes working in larger shops where they can't get management to swallow a 6 figure monitoring cost.

If you want to monitor query performance you need to read up on query fingerprints, and fully read though some Grant Fritchey books on how plans are cached. It's hard going and even he makes mistakes; it's MCM level.

After that you'll understand a little but even the paid tools won't point out when something has gone wrong. What they do point out are the top queries (in CPU, disk, memory), or weird memory grants, or missing indexes. You can do and store these easily from the internet but they aren't alertable thresholds. They never are.

On your own you might also be able to pinpoint excessively different plan recompilations based on fingerprints and execution details - but likely this isn't worth it otherwise commercial tools would do it. It's probably too noisy.

There's another book called Healthy SQL which covers a lot of ideas and thresholds but it gets hazy.

Another great place is to download the Performance Analysis of Logs tool (PAL) which has a list of SQL counters (along with disk IO counters and similar things) plus defines in XML exactly how it calculates thresholds and why. These are useful overall but again I'm not sure I'd alert on them except as a daily report.

I think some company has also published a semi marketing white paper on why thresholds are pointless and what you really need are baselines (and doing statistical analysis on standard deviations but I'm pretty sure they don't use it for much themselves). But IMHO nobody is sharing easy to understand ways to analyse and flag disparate raw baseline data between snapshots. R looks like a promising spot to do it but there's no "how".

  • Wow, thank you a lot for your time on answering that. We actually have 6 servers to monitor (Different environments, and they want to monitor all). My idea is exactly to have some baselines of my servers, but to create them you first have ot know from what counters you want to create them, and I feel like I'm missing plenty of them. – J1mmy Sep 2 '16 at 14:56
  • By the way, I don't want to create the "Definitive T-SQL Monitoring Tool" I just want to have key kpis to monitor in order to detect some "strange patterns" or problematic behaviours, we are going to host very small BI applications and transactional applications. – J1mmy Sep 2 '16 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.