Storage performance via TSQL

Storage performance via TSQL

On SQL 2000 looking at I/O statistics is achieved using fn_virtualfilestats

SELECT 
   d.name as DBName
   ,RTRIM(b.name) AS LogicalFileName
   ,a.NumberReads
   ,a.NumberWrites
   ,a.BytesRead
   ,a.BytesWritten
   ,a.IOStallReadMS
   ,a.IOStallWriteMS
   ,CASE WHEN (a.NumberReads = 0) THEN 0 ELSE a.IOStallReadMS / a.NumberReads END AS AvgReadTransfersMS
   ,CASE WHEN (a.NumberWrites = 0) THEN 0 ELSE a.IOStallWriteMS / a.NumberWrites END AS AvgWriteTransfersMS
FROM ::fn_virtualfilestats(-1,-1) a 
INNER JOIN sysaltfiles b ON a.dbid = b.dbid AND a.fileid = b.fileid
INNER JOIN sysdatabases d ON d.dbid = b.dbid
ORDER BY a.NumberWrites DESC

For SQL 2005+, sys.dm_io_virtual_file_stats is a available. This is a dynamic management function that shows how SQL is using the data files.

SELECT
 DB_NAME(filestats.database_id) AS DBName
 ,files.name AS LogicalFileName
 ,num_of_reads AS NumberReads
 ,num_of_writes AS NumberWrites
 ,num_of_bytes_read AS BytesRead
 ,num_of_bytes_written AS BytesWritten
 ,io_stall_read_ms AS IOStallReadMS
 ,io_stall_write_ms AS IOStallWriteMS
   ,CASE WHEN (num_of_reads = 0) THEN 0 ELSE io_stall_read_ms / num_of_reads END AS AvgReadTransfersMS
   ,CASE WHEN (num_of_writes = 0) THEN 0 ELSE io_stall_write_ms / num_of_writes END AS AvgWriteTransfersMS
FROM sys.dm_io_virtual_file_stats(-1,-1) filestats
INNER JOIN sys.master_files files
  ON filestats.file_id = files.file_id
  AND filestats.database_id = files.database_id
ORDER BY num_of_writes DESC

Further info on here c/o David Pless

If Disk Performance is an issue, consider these aspects

• Auditing disk configuration
• Physical file fragmentation
• Is the RAID configuration appropriate for volume of transactions.
• Is hardware behaving? Drive Controller cache ok?

SQL Server - Performance Monitor Quick Check

These are the first basic steps to take when looking at a sql server instance.
Look at hardware resources in this order

1 Memory 
2 Storage 
3 CPU

This is because low memory can demand storage to temporarily store committed memory.
If memory demand causes Windows to page this will increase CPU demand.
Basically one issue can be a symptom of another.

Memory

Look at these 4 Performance Counters.

Look at these counters -

Memory/Available MBytes - Ensure there is adequate memory for Windows, leave 200MB free.
Remember to prevent SQL from consuming too much memory by setting the maximum memory.

SQLServer:Memory Manager/Target Server Memory (KB)
This is how much memory SQL wants

SQLServer:Memory Manager/Total Server Memory (KB)
This is how much memory SQL has.
If this is less than the value it wants then more memory is needed to be allocated or installed.

SQLServer:Buffer Manager:Page Life Expectancy
300 seconds (5 minutes) for an OLTP system.
90 seconds (1 & 1/2 minutes) from a data warehouse.

SQLServer:Buffer Cache Hit Ratio is no considered a useful measure as it can still be high when other counters indicate problems.

Storage

You can use Performance Monitor to provide top level information of how Windows sees drives performing.

Examine using Perfmon for Windows view of Storage performance
LogicalDisk:Avg.Disk sec/Transfer
This is the time in seconds to transfer data to disk
Disk transfers should be < 20ms (0.020 seconds) for volumes hosting sql data files

This metric can be further analysed by breaking into reads & writes.
LogicalDisk:Avg.Disc sec/Read
LogicalDisk:Avg.Disc sec/Write

Differences between Read & Write figures could lead you to investigate a number of items e.g. RAID type, Drive Controller Cache, Sector Alignment.

CPU

Performance Monitor (Perfmon) counters to determine processor use are -

Processor: % Processor Time 
Use this as an idea of how busy the server is relative to capacity.

Processor: % Privileged Time 
Time spent managing server resources (not running applications)
Microsoft say over 30% is bad.

Processor: % User Time 
Time spent running applications

Process: %Processor Time : sqlservr 
This counter enables you to determine SQL server cpu usage.
Use it to prove that high CPU usage is due to sql (or not).

How they relate -

Processor Time = Privileged Time + User Time

Checksum vs Torn Page Detection

Torn Page Detection
Writes a bit for every 512 bytes in the page

• Detects when a page was not successfully written to disk
• Does not detect if all bytes were written accurately

Checksum
Calculates a checksum value before passing data to the IO system (which then writes data to the disk).

• Guarantees that SQL Server had no part in corrupting the page.
• Detects corruption (by IO system) when page is read from disk.
• Checksum error (824) will be generated if data differs.

Ref : Whats best: Torn Page Detection or Checksum?

Goals for 2011

A new role

From attending SQLBits, user group meetups and generally fraternising with the SQL community I’ve realised that job titles are used very interchangeably. Responsibilities vary wildly within DBA and DB development roles and not just in smaller companies. Some DBAs develop, some developers administer servers. Where ‘Analyst’ , ‘Consultant’ or ‘Business Intelligence’ appears in a title it may (or may not) hide a plethora of database skills.

Personally (regardless of job title) I want to build on what I’ve achieved and continue to home my skills this year. The knowledge gained from administration makes put me watch server resources and question my development efforts (which I think is good). Adrian Hills (@adathedev)  blogged on exactly this topic last year – The rise of the DevBa.

I’m especially keen to develop larger SSIS solutions following last year’s projects. In an ideal world there would be a chance to build Analysis Services Cubes too.

Blogging

Whilst I continue to put any script I could ever need again up here, I need to write more as articles. I’d like to think I will post less frequently but with better quality posts, i.e. written explanations. We shall see if i can achieve this...

Community
 
Publish some more scripts
I love getting the feedback when other’s find them useful and equally love learning new things when others improve them.

Publish more articles
I found this a lot harder than I thought hence I’m not going to commit to a number. I’d like to write something that others find useful.

Continue attending SQL Server events
Meeting like minded people when you work isolated for small companies is essential for your sanity.

Learning

SSAS
Get some exposure to an Analysis Services (SSAS) project following last year's Business Intelligence studying and certification.

.NET
Further my development skills by looking further at the .NET framework languages (Have done a splattering of VB.NET in CLR)

Reading
Read more! Tackle my book backlog as I have a small library of unread books, printed articles etc.

Bookmark : Running 2 Skype Accounts

Create a shortcut to (or just use Start > Run  & type...)

"C:\Program Files\Skype\Phone\Skype.exe" /secondary

Link : How can I run two Skype accounts on the same computer?

Send email via Database Mail from command line

I use this from batch files to show jobs scheduled in windows have completed.

osql -E -d databasename -S servername -Q "EXEC msdb.dbo.sp_send_dbmail @profile_name='mail profile' , @recipients='sql_admin@mydomain.co.uk' , @body='message body' , @subject='Alert!' , @importance='High' " 

MCM Video files

There's a lot of love on the twittersphere for the free SQL Server 2008 MCM Readiness videos and a couple of scripts have emerged to save time downloading them.

Firstly, back in December, Aaron Nelson (blog | twitter : @sqlvariant) developed a powershell script to automatically download them...


Powershell script to download sql mcm videos


Today, David Howard (blog | twitter : @daveh0ward) has blogged on mass renaming those files to more meaningful names (admittedly primarily so they are named sensibly on the iphone)


Renaming mcm video files with Powershell


The only problem now is finding the time to watch them all...