Optimizing SQL server can be a daunting task. There is a fairly large learning curve, and knowing where to start can be a problem. Over the weekend, I read over a great article on query optimization for SQL Server and found the greatest query ever.
SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESCThis query will list the top 20 most read intensive queries. What is great is it is based on total reads, so it is a quick and dirty way to find the worst queries in your system. Eliminating the top few queries on this list will give you the best bang for your buck.
I discovered one of the scariest performance problems with SQL Server I have ever seen today. This problem is so easy to over look, yet extremely detrimental to database performance.
Consider the following table.
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Users
(
id int NOT NULL,
username varchar(50) NOT NULL
)
GO
ALTER TABLE dbo.Users ADD CONSTRAINT
PK_Users_id PRIMARY KEY CLUSTERED
(
id
)
GO
CREATE UNIQUE NONCLUSTERED INDEX IX_Users_username ON dbo.Users
(
username
)
GO
COMMITNow look at the query plans of the following queries, that differ by a single character.
If you look closely, the query that uses an nvarchar parameter does an index scan while the one that users varchar does an index seek. This is very important, because an index seek is orders of magnitude faster than a scan.
The reason this occurs is because the parameter and column have different collation sets. Thankfully, I ran across this article which helped me solve the problem as it related to NHibernate.
The past few days, I have been playing around with mirroring on SQL Server. Even though there is a GUI to guide you through setup, it isn’t that easy until you understand a few things. I won’t go into details since there are tons of great resources to help setting up mirroring, I will just point you towards some of those resources.
- The step-by-step guide - This has most of the info you need for mirroring, but it is way too long. Still a great article though.
- Servers should be in a domain - The first time I tried this, I tried it without a domain controller and DNS. It was a pain. Adding the servers to an AD domain makes it a ton easier. Also, most everyone with SQL Server already has an AD domain, so this is a no brainer.
- SID’s are a pain - In a previous post, I talked about the orphaned user problem that backup restores have. Well, mirroring has the same problem, but a different solution. Since the witness server controls failover, you can’t rely on a script to fix the user mapping when something fails over. Instead you have to create a login with the same SID on both the principal and partner servers. This article talks more about SIDs and the MSDN site has info on CREATE LOGIN syntax.
Once mirroring has been setup correctly once, the next time is a lot easier. I would highly recommend creating deployment scripts that can set up your entire DB environment because SQL Server setup just has too many steps that are easy to forget or miss.
Backing up a SQL Server database and restoring it to another server is a common operation done for reporting, testing, or development. When restoring on a different machine, a common problem is the login principals don’t actually correlate to the restored database users. This is because they are linked by an SID. This SID isn’t the user name, its some hash value, probably a GUID. The problem is fully described in this article.
Fixing the problem is really simple. Just run the following SQL and substitute and with the appropriate values. I added these lines to all my Powershell scripts that are used to restore a database and it works like a charm.
USE <database_name>;
GO
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>';
GO
Today I wrote my first MsBuild script that uses sqlcmd.exe to execute SQL Server commands remotely. It is awesome. Although it is fairly limited, for tasks like backing up databases, it is very simple and easy.
<Target Name="Backup">
<Exec Command='sqlcmd -S $(server) -q "$(sql)"' />
</Target>
