One of the most important methodologies to date for troubleshooting problems with the SQL Server engine is examining the wait statistics. As with most of the DMV’s provided in SQL Server 2005/2008 sys.dm_os_wait_stats is a cumulative view. To add to my series SQLDIY: Manage and Monitor SQL Server Yourself here is my stored procedure for capturing deltas on an ongoing basis. It is modeled the same way I capture sys.dm_io_virtual_file_stats. This procedure captures the delta and the raw data at a point in time. This allows you to do your own roll ups to look at the numbers in different ways. More >
Recently I was troubleshooting a piece of software that archives data out of a very active import table. It is a pretty simple app. It queries the table for any id’s that have data older than X days. It then queries the table again to pull the detail data generating a csv file, table schema script and compresses them for storage. Well, it was running slow. Much slower than expected in fact. It has to process around 5000 id’s and it was taking 50 to 240 seconds an id. This doesn’t sound slow but it adds up in a hurry. I started digging into the code looking at the normal More >
It’s that time again, T-SQL Tuesday is here! This time Pat Wright (blog|twitter) is hosting and has put forth automating tasks using ether T-SQL or Powershell. I LOVE automating stuff. As a production DBA in some very large shops you can’t do your job unless you make your servers work for you. I’ve been using T-SQL and *GASP* xp_cmdshell, OSQL and file shares to gather stats and push configurations to servers for decades. Log before fancy things like C# and Powershell existed. These days I use a variety of home grown tools but doing things with just T-SQL can be just as powerful. I’m going More >
I recently saw a tweet about Idera’s new SQL Doctor tool that is currently in beta. This differs from other tools you may think of like Diagnostic Manager. DM and other tools like it gather some of the same information but are geared for real time alerts. This is more like the Best Practices Analyzer. It takes a look at several key points on your SQL Server instance and the OS. It makes recommendations on how to improve any problems it identifies. You may ask, does this do anything I can’t do on my own? No, it doesn’t. If you are doing this in house with your own tool set you may not need More >
Ok, now that I have your attention this really should be titled the danger of not qualifying all objects, but that isn’t quite as sensational enough to make you click through to here
Imagine if you will, a developer expects ten records to be inserted into a table and the query has been running for almost an hour. A quick glance and sp_who2 shows that it is using a lot of IO, I mean a lot. The first thing that happens is a command decision from the boss to kill the query. I took a quick peek and found the plan still in the plan cache while we waited for this thing to roll back. As I look at More >
SQL Server is a huge product with lots of moving parts. Bugs happen. Microsoft has a place to voice your issues or problems. They allow you to vote on the issue and then decide when or if it will get fixed. I’ve used Connect when I hit a bug and I have voted on items that were important to me. Recently I hit a bug in sp_createstats. I use this system stored procedure generate statistics in an automated process I’ve got that manages statistics. I added a new vendor database to the system and on the first run hit “Column ‘DAYSOPEN’ in table ‘dbo.TBL_OPPORTUNITY’ cannot be used in an More >
As promised and update on what has happened so far. A correction needs to be made. the P800 is a PCIe 1.0 card so the bandwidth is cut in half from 4GB/sec to 2GB/sec.
My CDW rep did get me in contact with an HP technical rep who actually knew something about the hardware in question and its capabilities. It was one of those good news, bad news situations. We will start with the bad news. The performance isn’t off. My worst fears were confirmed.The Hard Disks
The HP Guy (changing the names to protect the innocent) told me their rule of thumb for the performance of the 2.5” 73GB 15K drives More >
I have had the pleasure of being a vendor, and technical support for both hardware and software products. I know it isn’t easy. I know it isn’t always possible to fix everything. The level of support I’ve received from HP on my current issue is just unacceptable. This is made more frustrating by the lack of documentation. The technical documents show capacity. How many drives in an array, Maximum volume size but nothing on throughput.Every benchmark they have seems to be relative to another product with no hard numbers. For example, the P800 is 30% faster than the previous generation.
I’m More >
Out of all the problems you can have with SQL Server troubleshooting connectivity issues can be the most challenging. When you factor in the complexities of Active Directory and SQL Server’s interaction with it fixing SSPI errors can be down right baffling.
At my company we are moving onto new hardware and along the way standardizing on SQL Server 2005 x64.Since this is all happening on new hardware I have the luxury of doing most of the work before we flip the switch. We had one migration under our belt and the second one was looking good when the SSPI came and decided to make sure I spent More >
Microsoft has always been pretty good at one thing, ease on install.
One of the things I always says is the greatest thing about SQL Server is any idiot can install it. The other thing I also say is the worst thing about SQL Server is any idiot can install it.
This has carried over to the other fine products that ship with SQL Server. The one thing I’ve had pretty good luck using as a novice since it was introduced is SQL Server Reporting Services. As someone who hasn’t had the best experience over the years with crystal reports SSRS was a breath of fresh air.
One of the things I’ve never More >