SQL Server Tacklebox- P33: This book, as with almost all books, started out as an idea. I wanted to accumulate together those scripts and tools that I have built over the years so that DBAs could sort through them and perhaps adapt them for their own circumstances. | 6 Monitoring and notifications In Chapter 5 DBA as Detective I demonstrated how poorly-designed code can bring your server to a crawl and offered some remedial measures. In this chapter I want to shift the focus onto code that has been tested and approved and moved to production but once there causes resource issues with regard to CPU RAM or Disk I O. It may not be that the code itself causes SQL Server to use too many resources it may be due more to some confluence of circumstances for example the code running at the same time as a maintenance process such as an index rebuild or code that results in an excessively high number of connections at the time the OS is also performing a memory-intensive process. I am going to show how DBAs can be notified when such performance issues arise and then how to proactively respond to them before the Help Desk is called or worse the user sends mail to you your boss your boss s boss and your mother and father all of whom will shake their heads disapprovingly. OK maybe that is just me but you get the point. If you are in an enterprise environment chances are you already have a monitoring solution in place for server administrators and you can piggy back off that alerting system and with luck it will include SQL-specific alerts that allow you to for example report blocked processes. If you don t have this luxury then you have two choices build your own monitoring solution or buy in a third-party solution. While you can certainly build your own the cost in terms of time and resources is going to be high. You can for example set up performance alerts using the native Windows Perfmon utility or by setting native SQL alerts which are part of SQL Agent. However scripting all of these alerts requires some level of skill and patience. Although I do not demonstrate setting native SQL Alerts in this chapter because to be quite honest they are not part of my overall monitoring and notification strategy Figure does show where you would
