Performance Monitor in SQL Server 2005
There's a lot of ways to increase performance of our databases in SQL Server 2005. And there's so many tutorials about these. Like implement index, use of stored procedure, etc. But actually, how we can monitor our database performance in SQL Server, so we could review our works.
In this tutorial, I'll try to give a simple tutorial about monitor SQL Performances.
Performance Monitor
Yap, it's that easy! Use performance monitor if you want to know about your database performance. Easy and simple. We maybe know about these features in Windows, but, how do we use it?
1. Open Performance Monitor in your Windows
*For WinXP you can open it by browse Control Panel > Administrative Tools > Performance
*For Windows Server 2003, you just click performance monitor in Administrative Tools in Start Programs
2. You can add more performance object monitor by simply click add button in menu bar (simbolize by plus button), so you can monitor your database performance.
Okay, we could use this feature now. But performance monitor is not a powerfull feature unless you understand about it's parameter to achieve good performance, right? Here some tips :
- Add memory object (Available bytes), check it's value! if your graph show more than 4Mb, well we can claim it as a good performance. This number should be low because Windows uses as much RAM as it can grab for file cache
- Add Processor object (%Processor Time), check it's value! The recommended value is less than 75%, if your graph show number higher than these, maybe you should add more processor, or do some tuning like partitioning and filegroup-ing and move some filegroups to other server.
- Add memory object (Pages/Sec), check it's value! The recommended value is fewer than 5. If this is too high, it means your system is compensating for a lack of RAM by paging to disk.
Well actually, there's another performance object you can use, but I won't explain all of those in here, because it'll be too time-consuming. These 3 object is commonly used by DBAs to monitor their server performance and their database performance. If your abilities in tuning your database is good, you can reduce your server workload, and also save your computer resources. :)
Display Estimated Time Plan
Yap, you also can check whether your database need indexing or not, by using Display Estimated Time Plan. Here the illustration :
Commonly, people use a normal table scan method for their tables. This method is used when we don't implement index or primary key in our tables. This method can cause more time when querying your database if your database is a VLDB (Very Large Database) and you have million rows in your table. So you need to implement index in your tables.
You can check estimated time plan comparison between table-scan method and index-method by using Display Estimated Time Plan feature. For example, try to check estimated time plan for your table with table-scan method. Check your database performance by use select query or more complex query to retrieve data in your tables. After the results is shown, select 'Query' in your menu bar, and click Display Estimated Time Plan. Move your mouse cursor to method's shown in there (it could be either table scan or index method), and it will pop up message contains Estimated CPU cost time. After that, try to implement index for your table/database. You can use Database Engine Tune Advisor or manually create index for your tables/database. You can compare the results before you implement index (use table-scan method) and after you implement index.
FYI : table-scan method isn't always be a bad method. It could be give a better performance if your database is small or just contains hundred to thousands rows in your tables.
Hope this post is usefull.
Regards,
Rangga Praduwiratna
*References : Sybex and Microsoft Press books for Implementing and Maintaining SQL Server 2005.