Brett Hawton – 7 September 2010 One of the most significant "grey areas" in SQL Server performance is the IO performance of your SAN, NAS or RAID array. To be fair, it's not easy for a SAN administrator to juggle limited resources in order to provide consistently good performance to SQL Server's widely varying IO requests. If you view SQL Server's IO demands through a tool such as ProcMon or FileMon one observes the wildly varying IO requests it generates. Interspersed amongst significantly varying rates of 64KB buffer pages reads are 512KB read-ahead reads and lazy writer writes of random dirty pages. Things are no clearer on the transaction log side. Depending on the amount of data altered as part of an implicit or explicit transaction, log writes vary in size from 512 bytes all the way up to multiple 60KB writes (the log never writes more than 60KB at a time (NOT the 64KB most DBA's believe would be the case). So we have both varying read and write sizes in terms of number of bytes and wildly varying rates of read and write requests. The trouble is that most disks/RAID arrays/SAN/NAS devices work in IO's per second. The SAN admin wants to know how many IO's per second a specific database and its attendant transaction log require. It's fiendishly difficult to convert the widely varying IO demands of SQL Server (both in IO size and requests per sec) into metrics understandable to an IO subsystem. Take a look at the TSQL script below. What it does is measure the performance of your IO subsystem (per SQL Server file) over a short period of time (typically 5 minutes). Run it on your SQL Server 2005 or 2008 production system at a really busy time and see what results it generates:

SET NOCOUNT ON

DECLARE @IOStats TABLE (
	[database_id] [smallint] NOT NULL,
	[file_id] [smallint] NOT NULL,
	[num_of_reads] [bigint] NOT NULL,
	[num_of_bytes_read] [bigint] NOT NULL,
	[io_stall_read_ms] [bigint] NOT NULL,
	[num_of_writes] [bigint] NOT NULL,
	[num_of_bytes_written] [bigint] NOT NULL,
	[io_stall_write_ms] [bigint] NOT NULL)
INSERT INTO @IOStats
	SELECT database_id,
		vio.file_id,
		num_of_reads,
		num_of_bytes_read,
		io_stall_read_ms,
		num_of_writes,
		num_of_bytes_written,
		io_stall_write_ms
	FROM sys.dm_io_virtual_file_stats (NULL, NULL) vio
DECLARE @StartTime datetime, @DurationInSecs int
SET @StartTime = GETDATE()
WAITFOR DELAY '00:05:00'
SET @DurationInSecs = DATEDIFF(ss, @startTime, GETDATE())
SELECT DB_NAME(vio.database_id) AS [Database],
	mf.name AS [Logical name],
	mf.type_desc AS [Type],
		(vio.io_stall_read_ms - old.io_stall_read_ms) / CASE (vio.num_of_reads-old.num_of_reads) WHEN 0 THEN 1 ELSE vio.num_of_reads-old.num_of_reads END AS [Ave read speed (ms)],
	vio.num_of_reads - old.num_of_reads AS [No of reads over period],
	CONVERT(DEC(14,2), (vio.num_of_reads - old.num_of_reads) / (@DurationInSecs * 1.00)) AS [No of reads/sec],
	CONVERT(DEC(14,2), (vio.num_of_bytes_read - old.num_of_bytes_read) / 1048576.0) AS [Tot MB read over period],
	CONVERT(DEC(14,2), ((vio.num_of_bytes_read - old.num_of_bytes_read) / 1048576.0) / @DurationInSecs) AS [Tot MB read/sec],
	(vio.num_of_bytes_read - old.num_of_bytes_read) / CASE (vio.num_of_reads-old.num_of_reads) WHEN 0 THEN 1 ELSE vio.num_of_reads-old.num_of_reads END AS [Ave read size (bytes)],
		(vio.io_stall_write_ms - old.io_stall_write_ms) / CASE (vio.num_of_writes-old.num_of_writes) WHEN 0 THEN 1 ELSE vio.num_of_writes-old.num_of_writes END AS [Ave write speed (ms)],
	vio.num_of_writes - old.num_of_writes AS [No of writes over period],
	CONVERT(DEC(14,2), (vio.num_of_writes - old.num_of_writes) / (@DurationInSecs * 1.00)) AS [No of writes/sec],
	CONVERT(DEC(14,2), (vio.num_of_bytes_written - old.num_of_bytes_written)/1048576.0) AS [Tot MB written over period],
	CONVERT(DEC(14,2), ((vio.num_of_bytes_written - old.num_of_bytes_written)/1048576.0) / @DurationInSecs) AS [Tot MB written/sec],
	(vio.num_of_bytes_written-old.num_of_bytes_written) / CASE (vio.num_of_writes-old.num_of_writes) WHEN 0 THEN 1 ELSE vio.num_of_writes-old.num_of_writes END AS [Ave write size (bytes)],
	mf.physical_name AS [Physical file name],
	size_on_disk_bytes/1048576 AS [File size on disk (MB)]
FROM sys.dm_io_virtual_file_stats (NULL, NULL) vio,
	sys.master_files mf,
	@IOStats old
WHERE mf.database_id = vio.database_id AND
	mf.file_id = vio.file_id AND
	old.database_id = vio.database_id AND
	old.file_id = vio.file_id AND
	((vio.num_of_bytes_read - old.num_of_bytes_read) + (vio.num_of_bytes_written - old.num_of_bytes_written)) > 0
ORDER BY ((vio.num_of_bytes_read - old.num_of_bytes_read) + (vio.num_of_bytes_written - old.num_of_bytes_written)) DESC
GO

Your results should look similar to what is shown below. The results are sorted in descending order by read+write activity so the most active files will show first and the least active last. There are a number of interesting columns which require further explanation however by far the two most important columns are Ave read speed (ms)/Ave write speed (ms) . On a modern high-quality SAN or RAID subsystem these figures should be less than 4 ms on average. Higher values indicate:

1. The IO subsystem is flooded with requests.
2. The LUN does not have sufficient devices allocated to it
3. That you are not using a RAID 10 configuration for writing and either a RAID 5 or RAID 10 configuration for reading.
4. That you are not using modern 15K disk drives affording low access times
5. That the devices are not correctly sector aligned
6. That write-back cache has not been enabled.

The other columns provide information to the IO administrator in order to correct sub-standard read and write speeds: The number of reads per sec, MB's read per sec and the average read size will provide the IO administrator with the metrics required to correctly configure the IO subsystem resulting in improved performance. On the write side it's essentially the same metrics again; number of writes per sec, MB's written per sec and average write size which will guide the administrator. So long as the script is run during SQL Server's busiest period then the results should give an accurate picture of where improvements need to be made and provide the IO administrator with accurate metrics in order to re-configure the IO subsystem in the best possible way.

Understanding current trends in business conditions often requires asking a lot of questions, but allowing users to run query after query against your operations databases causes more problems  than it solves. By creating a data mart, you can maintain a separate data store that can withstand the heavy query load that business analysis demands. In this webcast, you'll learn why you need a dimensional data model for your data mart, how to quickly build a dimensional model, and how to use control flow and data flow components in Integration Services to build simple packages that populate the dimensional model with data.

Stacia Misner is a consultant, educator, mentor, and author specializing in business intelligence solutions since 1999 with more than 25 years of experience in information technology. She is the author and co-author of eight books about business intelligence, with Microsoft SQL Server 2008 Reporting Services Step by Step and Introducing SQL Server 2008 R2 as her most recent books. Stacia has taught all aspects of Microsoft BI around the world, enlivening the course materials with insights, tips, best practices, and plenty of interaction to help students connect theory to actual practice. Stacia provides consulting and custom education services through Data Inspirations, writes about her experiences with BI at blog.datainspirations.com, and tweets as @StaciaMisner.

Register now!

A key component of maintaining successful applications is to stay ahead of performance requirements. The more an application is used, the more attention is required to keep up with demand and adapt to changing business requirements. One way to keep up is to throw more and more hardware at problems as they crop up, however a better option is to maximize existing hardware and resources. This paper provides a list of the Top 10 ways you can improve SQL Server performance with the hardware you already own.

Read more!

INDUSTRY University Medical Center (UMC) is a private, non-profit teaching hospital located at the Arizona Health Sciences Center and adjacent to The University of Arizona in Tucson, Arizona. Its 4,000 employees support more than 20,000 admissions and almost 400,000 outpatient visits annually. UMC’s high nurse-to-patient ratio contributes to its outstanding patient care, ranking it among the nation’s premier hospitals in U.S.News & World Report’s annual guide to “America’s Best Hospitals.” It has also been named one of the 100 Top Hospitals by Thomson Reuters.


BUSINESS CHALLENGE Database Administrator Tom Ryan is tasked with the administration of the hospital’s SQL Servers: determining whether backups are being run, dealing with issues of blocking, and making sure the SQL Servers are running smoothly. With 122 SQL Server instances, Ryan needed a high-performance backup and recovery solution that would meet UMC’s rapid growth. He also sought to reduce backup and recovery times and reduce the amount of space required for backups.


IDERA SOLUTION Idera’s SQL safe allows University Medical Center to look at all instances in one centralized console and quickly view backup status. The “per instance” pricing model offered by Idera also helps UMC keep costs down as they are not required to have a separate license for back-up restores. Within one week of switching to SQL safe, UMC discovered that they were able to compress and reduce their backup files by hundreds of gigabytes, saving money on SAN, disk and off-site tape storage.

Read more!

High availability solutions are the pro-active counterpart to a solid disaster recovery plan and are increasingly becoming more and more commonly deployed. If you’ve already deployed an HA solution or are planning on it, join independent SQL Server expert Michael K. Campbell for a free webcast that provides an overview of the top mistakes and problems organizations are prone to make when deploying HA solutions.

Speaker: Michael K. Campbell is an independent SQL Server consultant and contributing editor to SQL Server Magazine. As a former DBA and Database developer for several well-known companies, he has over 11 years of experience of working with SQL Server and has written articles and given presentations on a wide variety of SQL Server Best Practices. He enjoys teaching and sharing insights about SQL Server and has made over four hours of free SQL Server Video tutorials available at www.sqlservervideos.com.

Watch now!

Microsoft has long billed the ability to extend SQL Server with the .NET Framework’s Common Language Runtime (or CLR) as a panacea to a myriad of woes. Join independent SQL Server expert Michael K. Campbell for a free webcast that will look at the hype, mystery, and confusion out of when and how to extend SQL Server with the CLR. By attending this webcast you’ll learn about potential CLR pitfalls, review best practices, and see first-hand how easy it is to extend SQL Server with CLR functionality.

About the Speaker
Michael K. Campbell is an independent SQL Server consultant and contributing editor to SQL Server Magazine. As a former DBA and Database developer for several well-known companies, he has over 11 years of experience of working with SQL Server and has written articles and given presentations on a wide variety of SQL Server Best Practices. He enjoys teaching and sharing insights about SQL Server and has made over four hours of free SQL Server Video tutorials available at www.sqlservervideos.com.

Watch now!

Problem

In some rare occasions it’s possible for organizations to 'forget' their sa password to an operational SQL Server. In cases where this occurs and no trusted Windows Logins (or groups) are part of the sysadmin server role on the server itself, there is seemingly no way to regain Sysadmin permissions.

Solution

In many ways this scenario is like locking your keys in your car when you need to be somewhere – because without the sa password it’s impossible to log in, and without any trusted users in the servers’ sysadmin server role, windows can’t grant administrative access either.

In cases like this (which should be extremely rare) many systems administrators assume that their only recourse is to power SQL Server down, copy the databases somewhere (as a precaution) and then uninstall/reinstall SQL Server and redeploy their databases.

Happily, there's a better way. To recover administrative access to a 'locked out' SQL Server, a member of the Local Administrators group on the host machine can restart SQL Server in single user-mode using the -m startup option (as outlined in Books Online) and by doing so they’ll be automatically added to the sysadmin server role – meaning that they can then either reset the sa password and/or assign users to the sysadmin server role.

Of course, if you’re astute, you’ll note that this ‘feature’ also provides a potential security threat of its own as it means users with administrative access can gain control of SQL Server data even when they haven’t been granted explicit permissions to do so. But, as they say, there’s “no free lunch” (especially when it comes to security). So make sure to have auditing and other safeguards in place if your data is extremely sensitive.

Tip by Michael K. Campbell, SQL Server consultant, author and founder of SQLServerVideos.com, home of high quality, FREE SQL Server instructional videos

Join  independent SQL Server expert Michael K. Campbell for a webcast that contrasts core SQL Server Security concepts against common security mistakes found in the wild. By attending this webcast you will:

• Review core security concepts such as the difference between auditing and security and address advanced topics like misconceptions about how to prevent SQL Injection.


• Learn about the perils of not adhering to the principles of least privilege and will learn tips, tricks, and tactics to help make the transition to least privilege much easier in your own environment.


• Learn about security concerns for data at rest and review ways to prevent wholesale circumvention of SQL Server security constraints for your data.

 
About the Speaker:  Michael K. Campbell is an independent SQL Server consultant and contributing editor to SQL Server Magazine. As a former DBA and Database developer for several well-known companies, he has over 11 years of experience of working with SQL Server and has written articles and given presentations on a wide variety of SQL Server Best Practices. He enjoys teaching and sharing insights about SQL Server and has made over four hours of free SQL Server Video tutorials available at www.sqlservervideos.com.

Watch now!

With the release of SQL Server 2005 and SQL Server 2008, Microsoft exposed new valuable data elements that can be used for managing your SQL Server.  These new data elements can be accessed via Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs).  In this webcast we will look at some of the most commonly used DMVs and DMFs and how they can be best put to use in your SQL Server environment.

Speaker: Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He has been working with SQL Server for the past 11 years and has authored numerous articles and delivered several presentations online and at local and national SQL Server events.

Watch now!

Check out this FREE ebook from Microsoft Press - Introducing Microsoft SQL Server 2008 R2