When you’re developing new Transact-SQL code or modifying some existing code, do you just launch directly into programming?
I know that I did just that, for years. It wasn’t until I was trying to performance tune some existing code that I realized I hadn’t actually taken caching of data and execution plans into account. So all those modified stored procedures that I was so proud of might not actually be faster than the first generation of procedures because I hadn’t checked to ensure that I was testing cached programs against uncached programs (and, by extension, the data used by those programs). That’s easy enough to fix with a test harness. Test harness were originally an actual, physical harness used by engineers to clamp down parts of an electrical or mechanical device they were prototyping. Ours is no different. It locks down all of the assumptions about our code (like my early, false assumption that I didn’t need to clear the caches) and adds a metric or two for good measure – literally – so we can better measure what’s happening in that code.
Here’s what my test harness looks like:
/* Transact-SQL test harness by Kevin Kline, http://KevinEKline.com, Twitter at kekline */
/* Flush dirty pages from the buffer to the database files. */
CHECKPOINT;
/* Flush the data cache and procedure cache, respectively. For DEV environments only! */
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
/* Enable statistics tracking for IO and timings. Remember, SET commands remain enabled during a session until disabled. */
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- Whatever SQL code you'd like to process goes below.
SELECT SalesOrderID
FROM Sales.SalesOrderHeader H
WHERE CustomerID = 344
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
/* Textual Execution Plans, if desired.
SET SHOWPLAN_TEXT ON;
SET SHOWPLAN_TEXT OFF;
*/
I also like to include the execution plans a lot of the time. You might wonder why I don’t save the execution plans for the GUI in SSMS? Well, I’m a big advocate of scripting in general because I like to automate activities. By pulling the execution plans using scripts, I can use SQLCMD to schedule a large number of query executions during the evening and have the results ready for analysis when I come back into the office in the morning. Workin’ smarter, not harder, Baby!
So how does this test harness work for you? Do you use other elements in yours? If so, share your experiences here!
I’d recently experienced a situation where I was getting repeated login failures to a SQL Server where I knew that I had the correct user name and password.Each time, I’d get error 18456 in response.
“Login failed for user ‘<user_name>’. (Microsoft SQL Server, Error: 18456)”.
The challenge when troubleshooting this error message is that you may have a problem with SQL Server or you may have a problem with Active Directory or Kerberos, if you’re using one of those authentication technologies.
A False Trail
When times are good, and you’re able to make a connection, you can always query sys.dm_exec_connections, to see how you’re connecting, for example, using NTLM rather than Kerberos.But that doesn’t help us when we can’t connect to the server at all.
Fortunately, my MVP buddies Edwin Sarmiento (blog | twitter) of Canada and Bitemo Erik Gergely (blog) of Hungaria had already discussed and solved the problem for me!
Something Erik pointed out, but hadn’t occurred to me at first, is that if you’re getting this SQL Server error message then you’ve actually reached the server and probably aren’t having a full disconnect error. The second thing that Erik pointed out is the importance of the state element of this error message.A lot of the time, you can simply ignore the
state element of an error message.But not this time. As it turns out, state is the key to solving the problem.For example, a state of 18 indicates that the password must be changed.
Even better for all troubleshooting situation involving state information, Microsoft has now added state descriptions for errors in Books Online, including error 18456.When you look in your SQL Server error log, you see the state of the error and be able to make an accurate deduction about the nature of the error!
Back when my day-to-day duties included database administration work and enterprise architecture, I became rather obsessed with the idea of operational excellence. I read everything I could on the topic. I made a list of favorites, which became somewhat shabby over time, as I dog-eared important pages and scribbled notes in the margins. (Perhaps that list of favorites might, in and of itself, make a good blog post). Fast-forward a decade and I’m still mightily interested in operational excellence for IT organizations. It’s just that so much good material is available for free on the web.
Here’s a run-down of several useful documents and downloads to improve overall operation performance for those of you in a Microsoft-centric IT organization:
Microsoft Operations Framework
Microsoft Operations Framework (MOF) version 4.0 guide is practical guidance for IT organizations. With the release of version 4.0, MOF now reflects a single, comprehensive IT service lifecycle—it helps IT professionals connect service management principles to everyday IT tasks and activities and ensures alignment between IT and the business.
Infrastructure Planning and Design
The Infrastructure Planning and Design (IPD) guides are the next version of Windows Server System Reference Architecture. The guides in this series help clarify and streamline design processes for Microsoft infrastructure technologies, with each guide addressing a unique infrastructure technology or scenario.
Microsoft Baseline Security Analyzer 2.2 (for IT Professionals)
The Microsoft Baseline Security Analyzer provides a streamlined method to identify missing security updates and common security misconfigurations. MBSA 2.2 is a minor upgrade correct minor issues and add optional catalog support.
Security Compliance Manager
The Microsoft Security Compliance Manager provides centralized security baseline management features, a baseline portfolio, customization capabilities, and security baseline export flexibility to accelerate your organization’s ability to efficiently manage the security and compliance process for the most widely used Microsoft technologies.
Quest Software’s latest community initiative, Windows Azure-based Project Lucy, has debuted! Project Lucy is part infrastructure analytics, part social media experiment, and part performance data warehouse.
The best things about Project Lucy include:
It’s Free – just like our SQLServerPedia website, Project Lucy is free to anyone who wants to upload a trace file
It’s 1oo% web-based – you don’t have to download or maintain anything and updates roll out seamlessly, all the time
It really helps – just generate a SQL Trace on a SQL Server 2000, 2005 or 2008 instance, upload it on www.projectlucy.com, and see for yourself
This initiative empowers DBAs and IT professionals to gain a better understanding of their database performance through automated analyses and collaboration with their peers. Our goal is to allow users to go beyond the benefits of installing and using a commercial product, and tap into an online community to share and compare experiences and results.
Our goal is to test the best ways we can help users learn and understand what their performance data really means, and how they should act upon that data – if at all – in a collaborative, meaningful way. For this debut, Project Lucy will accept SQL Trace files, or zip files containing multiple traces, and will provide a summary of the content and areas where performance can be improved. As y’all use the service, information will become available to help you compare your performance and engage in meaningful discussions with others about the issues you are facing.
Your feedback, your feedback, and participation will drive how Project Lucy evolves. Get involved by participating in the pilot program! And let me know what you think.
We all know how hard troubleshooting SQL Server can be. There are tools and techniques that can make troubleshooting much easier, such as the Windows System Monitor.
The only problem with SysMon, or more commonly “PerfMon”, is that it takes a lot of knowledge and experience to know which objects and counters to examine.
I’ve put together a nice poster for you covering all of the important PerfMon counters for SQL Server troubleshooting, with a little help from some of my friends: Brent Ozar, Bob Ward, Christian Bolton, and Raoul Illyes.
You Can Has It Fer Free!
If you live outside of North America, get the hi-resolution PDF here.
If you live in North America, order the poster here, and it’s free including postage anywhere in the USA and Canada.
PerfMon is Irie, Mon!
But Wait There’s More!
And just in case you didn’t watch our virtual conferences back in March or in July, you can watch my “PerfMon is Irie, Mon” session that describes how to use Windows System Monitor and some related tools, all in my rasta-man costume.
Order a free copy of the events on CD here. Restrictions apply.
