Industry experts will tell you that virtualization of your environment is not done to improve performance, it’s done to make it cheaper and easier to manage. The task of most VM administrators is to cram as many VMs into a farm as possible. One of the ways is to accomplish that is to allocate “thin provisioned” storage to each VM.For each VM that is created, the VM admin has to specify the number of virtual CPUs, the amount of virtual RAM, the number and size of each virtual disk, as well as a few other items. The virtual disks can...
Continue reading...2013
Investigating Plan Cache Bloat
SQL Server includes a DMV, sys.dm_exec_query_stats, that returns performance statistics for each query plan cached in memory. However, it can also help give you insight into how consistent your developers are with writing code.For this topic, we’ll just concentrate on a few columns returned by the DMV: sql_handle and plan_handle. Per Books Online, sql_handle is a value that refers to the batch or stored procedure that the query, and plan_handle is a value that refers to the compiled plan of that query. For each query that is processed, SQL Server can generate one or more compiled plans for that query. This one-to-many relationship can be caused by a number...
Continue reading...An Alternative to SELECT COUNT(*) for Better Performance
Sometimes rapid code development doesn’t always produce the most efficient code. Take the age old line of code SELECT COUNT(*) FROM MyTable. Obviously this will give you the row count for a table, but at what cost? Doing any SELECT * from a table will ultimately result in a table or clustered index scan. Turning on STATISTICS IO on reveals 5753 logical reads just to return the row count of 776286.Table ‘FactProductInventory’. Scan count 1, logical reads 5753, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Starting with SQL Server 2005, Microsoft introduced a...
Continue reading...Merging SQL Server and Softball Just for Fun
With opening day of Major League Baseball season finally here, I thought I’d take the time to cover two of my favorite topics…SQL Server and softball. Have you ever thought about how you can use SQL Server in conjunction with softball? Ok, so maybe you haven’t, but I have. I have been managing a slow-pitch softball team, the Sons of Pitches, for the past 5 years. Yes, I did say “slow-pitch”. My friends and I have already passed the peak of our physical ability, so we use it as an excuse to get together and have a little fun. As a DBA, I’m...
Continue reading...Use Powershell to Pick Up what Database Mirroring Leaves Behind
Database mirroring has been around since SQL Server 2005, and it’s turned out to be an excellent step up from log shipping. However, like log shipping, it is still only a database-level disaster recovery solution. Meaning that any logins, server role memberships or server-level permissions will not be mirrored over to the mirror server. This is where the DBA needs to plan ahead and create their own custom jobs to script and/or document these types of shortcomings. My solution is to use Powershell. In this example, I have setup database mirroring for the AdventureWorks2012 database. For this demo, both instances, TEST1 and TEST2, are on...
Continue reading...
