Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server - How much resource is a SQL database using - memory, disk, IOPS,CPU

22 January,2013 by Jack Vamvas

How much resource is each SQL database using?. There are different types of resource on the database server. Memory , disk,  IOPS, CPU.

The main difficulty with defining the resource usage for a specific database is the wide impact of one query across multiple databases. For example a query with a SORT may spill into the TempDB.

Other examples , are joins and transactions across more than one database

 These links to post showing techniques to isolate different resource usage. Analysing the resource usage from different angles , should give you a rough idea of usage .  Use this information to make decisions such as – add another database ? or move a database ? or add more resource to the server.

 If you’re  tuning – tuning at the database level can be frustrating – which is why it’s more effective at the query level .

 Memory 

 SQL Server – Calculate Database memory usage with sys.dm_os_buffer_descriptors  

Database Size – extra disk space

SQL Server Database Size and Free Disk Space ... - SQL Server

IOPS 

Measure current read & writes . Do the server drives have capacity?

 SQL Server measure i\o transaction rates and sys.dm_io_virtual_file_stats

CPU Utilization

 This one is difficult – I’ve achieved the best results  using the DMV - sys.dm_exec_query_stats . Read more on SQL Server – Find high impact queries with sys.dm_exec_query_stats  

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer