If you don’t know about the Old Spice Guy, seriously…go hit youtube.com now.
The past few weeks we’ve been preparing for a conversion of data from two systems into two different systems. I built a part of that process. Tonight was the first run. Everything went smoothly once we worked out a few details that we’d collected from another team that was a part of the conversion. That is until we got to one particularly nasty query.
This query allows our process to gather files stored in binary fields in a database, and store them on the file system of another server. The part that makes it nasty is it was built so that it could be run as a multiple process query. Meaning the total number of files would be divided up into 10 or more processes, and each query would get their share of the work. I’ve never mastered this type of query. Every solution I’ve come up with either would cause collisions between processes, or take much longer than necessary to complete the work.
I had tried helping the programmer in making this query as quick as possible, and at it’s best could process approximately 15,000 records per minute. Not horrible, even when dealing with about a half a million records. But when we went to production, we were processing closer to 1,000 records per minute. Something was definitely wrong.
I asked the assigned DBA to look at the work being done on the server, maybe there was some locking or blocking causing us an issue.
No dice.
I took one of the example queries and asked the DBA to run the data tuning advisor, perhaps the indexes I’d developed on the testing server were not appropriate for the production server. It suggested no new queries.
RED ALERT… but more on that in a second.
I took the query and ran it against a different database on the same server. It improved a bit, about 5,000 per minute. I was lost, until I remembered some sage advice from Grant Fritchey, and several others from SQL Saturday #46 in Raleigh, NC… never rely on the data tuning advisor.
So I took the query, and cut it down to only look at 1000 records (TOP 1000), I figured if I could tune that, the full query would come online. So I SET STATISTICS IO ON and took a look. (I changed the table names to protect the innocent.)
Table 'table1'. Scan count 0, logical reads 211, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'table2'. Scan count 0, logical reads 100638, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'table3'. Scan count 100, logical reads 1053, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'table4'. Scan count 0, logical reads 700, physical reads 30, read-ahead reads 76, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'table5'. Scan count 9, logical reads 800, physical reads 0, read-ahead reads 101, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Can you tell which table I zeroed in on? That’s right table2! I did a quick SELECT * to see how big the overall table was.
Table 'table2'. Scan count 0, logical reads 90, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
WOW that query is jacked, or I could really use an index. I checked SSMS for indexes….
None found. Let’s look at the query a bit closer… Hrm, it’s joining to that table on one column, and returning another.
CREATE NONCLUSTERED INDEX ix_table1__column1 ON table1 (column1 ) INCLUDE (column2)
That caused the IO for table two to drop to 78 scans, and the logical reads dropped to under 100. We could now process 80,000 records in just over 16 seconds. This was awesome… even though it was just after 4 am at this point, and I was getting a bit loopy. I was able to come up with this gem:
Hello, ladies, look at your query, now back to my query, now back at your query, now back to my query. Sadly, yours isn’t mine, but if you use my index, your query could run like mine. I’m on a horse. Indeed…. I’m on a horse!