Friday, July 23, 2010
If you are interested in finding out the largest SQL projects in the world, you should check out this PowerPoint presentation. It’s from Kevin Cox of SQL CAT at Microsoft.
Friday, July 16, 2010
SQL Saturday is coming to my hometown, San Diego, on September 18th, 2010. I have submitted my session and hope that it gets approved.
Let me know if anyone is attending the event in San Diego. If you aren’t in Southern California, check their site for the location nearest you.
Friday, July 09, 2010
A while back I wrote a CLR object to track database growth. The CLR object queries sp_databases for each passed in server and saves the data into a table. By using this CLR object, I can track all of my systems in one location. I blogged about this tool in this article.
A few weeks ago, I noticed that my SQL Agent job that runs this CLR object was failing. It was only failing on one of my database servers, so I ran sp_databases on that system to figure out what was going on. I noticed that for one database, the DATABASE_SIZE column was NULL. After doing some digging, I realized that sp_databases has a bug in it for any database that is 2TB in size or greater.
Sp_databases is using int data type for the DATABASE_SIZE column. Well that’s not big enough for a 2+TB database! A bigint should have been used instead. For backward compatibility reasons, Microsoft decided to display NULL for this situation rather than an error in SQL Server 2005 and 2008. In SQL Server 2000, it throws an error.
I found out that there is no bug fix for this, so I had two options. The first option would be to manually patch sp_databases on each of the servers. The second option would be to patch my CLR object. If I patched my CLR object, I could either create a new stored procedure, perhaps name it sp_databases2, or put the query directly into the C# code.
I didn’t like the option of patching sp_databases on potentially hundreds of servers even though I could easily deploy it using a batch file that calls sqlcmd. I don’t like the idea of modifying Microsoft’s code even though it’s a simple bug fix.
With the second option of patching my CLR object, I didn’t like the idea of creating a new stored procedure as that adds a dependency to my tool. So I instead decided to patch the CLR object by putting the correct query directly in the code.
You can download the new version here. Please reference the old article for how to call it, and especially how to call it for all of your servers. The old article also links to the new version of the code, since I didn’t change functionality.
Here’s the query in sp_databases that has a bug:
select
DATABASE_NAME = db_name(s_mf.database_id),
DATABASE_SIZE = convert(int,
case -- more than 2TB(maxint) worth of pages (by 8K each) can not fit an int...
when convert(bigint, sum(s_mf.size)) >= 268435456
then null
else sum(s_mf.size)*8 -- Convert from 8192 byte pages to Kb
end),
REMARKS = convert(varchar(254),null)
from
sys.master_files s_mf
where
s_mf.state = 0 and -- ONLINE
has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
group by s_mf.database_id
order by 1
And here’s the bug fix:
select
DATABASE_NAME = db_name(s_mf.database_id),
DATABASE_SIZE = convert(bigint, convert(bigint, sum(s_mf.size))*8),
REMARKS = convert(varchar(254),null)
from sys.master_files s_mf
where
s_mf.state = 0 and
has_dbaccess(db_name(s_mf.database_id)) = 1
group by s_mf.database_id
order by 1
Friday, July 02, 2010
Last November, I blogged about a weird bug with SQL Server 2005 on a Windows 2008 cluster. We were having issues with Database Mail and other things and learned that it was due to the server names being in lower case. To workaround the issue, we had to follow the steps outlined here as there was no bug fix at the time. I’ve now learned that this bug has been fixed! The fix is included in cumulative update package 9 for SQL Server 2005 service pack 3. Check it out here.
Monday, March 08, 2010
I recently received the following email from a blog reader:
"We are having an OLTP database instance, using SQL Server 2005 with little to moderate traffic (10-20 requests/min). There are also bulk imports that occur at regular intervals in this DB and the import duration ranges between 10secs to 1 min, depending on the data size. Intermittently (2-3 times in a week), we face an issue, where queries get timed out (default of 30 secs set in application). On analyzing, we found two stored procedures, having queries with multiple table joins inside them of taking a long time (5-10 mins) in getting executed, when ideally the execution duration ranges between 5-10 secs. Execution plan of the same displayed Clustered Index Scan happening instead of Clustered Index Seek. All required Indexes are found to be present and Index fragmentation is also minimal as we Rebuild Indexes regularly alongwith Updating Statistics. With no other alternate options occuring to us, we restarted SQL server and thereafter the performance was back on track. But sometimes it was still giving timeout errors for some hits and so we also restarted IIS and that stopped the problem as of now."
Rather than respond directly to the blog reader, I thought it would be more interesting to share my thoughts on this issue in a blog.
There are a few things that I can think of that could cause abnormal timeouts:
- Blocking
- Bad plan in cache
- Outdated statistics
- Hardware bottleneck
To determine if blocking is the issue, we can easily run sp_who/sp_who2 or a query directly on sysprocesses (select * from master..sysprocesses where blocking <> 0). If blocking is present and consistent, then you'll need to determine whether or not to kill the parent blocking process. Killing a process will cause the transaction to rollback, so you need to proceed with caution. Killing the parent blocking process is only a temporary solution, so you'll need to do more thorough analysis to figure out why the blocking was present. You should look into missing indexes and perhaps consider changing the database's isolation level to READ_COMMITTED_SNAPSHOT.
The blog reader mentions that the execution plan shows a clustered index scan when a clustered index seek is normal for the stored procedure. A clustered index scan might have been chosen either because that is what is in cache already or because of out of date statistics. The blog reader mentions that bulk imports occur at regular intervals, so outdated statistics is definitely something that could cause this issue. The blog reader may need to update statistics after imports are done if the imports are changing a lot of data (greater than 10%). If the statistics are good, then the query optimizer might have chosen to scan rather than seek in a previous execution because the scan was determined to be less costly due to the value of an input parameter. If this parameter value is rare, then its execution plan in cache is what we call a bad plan. You want the best plan in cache for the most frequent parameter values. If a bad plan is a recurring problem on your system, then you should consider rewriting the stored procedure. You might want to break up the code into multiple stored procedures so that each can have a different execution plan in cache.
To remove a bad plan from cache, you can recompile the stored procedure. An alternative method is to run DBCC FREEPROCACHE which drops the procedure cache. It is better to recompile stored procedures rather than dropping the procedure cache as dropping the procedure cache affects all plans in cache rather than just the ones that were bad, so there will be a temporary performance penalty until the plans are loaded into cache again.
To determine if there is a hardware bottleneck occurring such as slow I/O or high CPU utilization, you will need to run Performance Monitor on the database server. Hopefully you already have a baseline of the server so you know what is normal and what is not. Be on the lookout for I/O requests taking longer than 12 milliseconds and CPU utilization over 90%. The servers that I support typically are under 30% CPU utilization, but your baseline could be higher and be within a normal range.
If restarting the SQL Server service fixes the problem, then the problem was most likely due to blocking or a bad plan in the procedure cache. Rather than restarting the SQL Server service, which causes downtime, the blog reader should instead analyze the above mentioned things. Proceed with caution when restarting the SQL Server service as all transactions that have not completed will be rolled back at startup. This crash recovery process could take longer than normal if there was a long-running transaction running when the service was stopped. Until the crash recovery process is completed on the database, it is unavailable to your applications.
If restarting IIS fixes the problem, then the problem might not have been inside SQL Server. Prior to taking this step, you should do analysis of the above mentioned things.
If you can think of other reasons why the blog reader is facing this issue a few times a week, I'd love to hear your thoughts via a blog comment.
Tuesday, January 19, 2010
Paul Randal recently blogged about three life-changing events that brought him to where he is today. His blog is a very interesting read and idea, and so I bring to you my three events that brought me to where I am today in my technical career as a SQL Server DBA.
Right out of high school and during the first two years of college, I worked as a student worker for the District Attorney's Office at the County of San Diego performing data entry and other forgettable tasks. During this job, I worked with a previous classmate whose mom worked in another division at the County of San Diego. It was communicated to her mom that I was interested in a technical career. The mom was able to get an interview for me with the DBA team in the Information Services division at the County of San Diego. The position was only as a student worker, but it was a technical position so I jumped at the opportunity when the job was offered to me. I remained a student worker in the DBA team until I graduated from college with a Bachelor of Science in Mathematics with emphasis in Computer Science. I was then hired full-time to be a Junior DBA in the same group.
I remained a Junior DBA until the IT department was outsourced to Computer Sciences Corporation. This is the second significant event. Rather than take the generous severance package that was offered to any employees who did not wish to be part of the outsourcing, I decided to instead transfer to CSC as an Intermediate DBA. CSC was located in San Diego, so I didn't need to move or commute far away. At CSC, I met my husband which is a very significant event in my life but not relevant to this blog.
After about two years at CSC, I was so unhappy with my management that it drove me to find another job. I had been interviewing for about six months without any success when I happened to be at an interview and saw a former co-worker there. It turns out that I was interviewing for his position which he had already vacated (he was just there visiting and picking up some of his things). I surely wasn't qualified for the position, but this chance encounter is the third significant event. He contacted me a few weeks later and let me know of a DBA position that had opened up in his group at the new company, Qualcomm Incorporated. I interviewed for it a few days later. Although they were looking for a Senior DBA, my former co-worker decided to take a chance with me since he had mentored me at the County of San Diego and was aware of my possibilities. They hired me as an Intermediate DBA. The rest is history.
I have been at Qualcomm for 8 years and love every minute of it. I am now a Staff DBA (one level higher than Senior). My mentor still works at the same company as me, however he has moved on to other things. I am eternally grateful to him for all of the things that I learned and for taking a chance when there were more seasoned candidates interviewing for the position.
If it weren't for my former classmate's mom landing me an interview for a student worker position in the DBA team, I don't know where I would be in my technical career. I suppose I would have eventually made it into a DBA position since the skills match me perfectly, but it probably would have been a long journey to get there.
If it weren't for transferring to the new company during the IT outsourcing, I may never have ended up at my current company where I absolutely love my job. I also wouldn't have met my husband.
And finally if it weren't for my mentor and former co-worker, I wouldn't be where I am today. I owe my career to him. Hopefully he accepts my gratitude instead of a monetary gift, haha!
Tuesday, December 29, 2009
I have modified isp_Backup, which is the stored procedure that I use to backup SQL Server 2005 and 2008 databases. This new version includes one new feature, which I think is critical to SQL instances with multiple databases on them.
In the old version, when a backup failure was encountered such as due to lack of free disk space, the stored procedure would end without proceeding with the other databases. Since the stored procedure backs up databases alphabetically, you could end up with many missed backups. To be more clear, let’s say we have a SQL instance with three databases on it: ABC, JKL, and XYZ. If we encounter a backup failure on ABC, JKL and XYZ will not be backed up until ABC finally succeeds at a later run.
In the new version, I use Try/Catch logic when performing the backups. When a backup failure is encountered, I capture the database name into an “array” and then proceed with the next backup. At the end, I raise an error (RAISERROR) if there were any backup failures and in that error show which databases failed to backup. By using the RAISERROR statement, our SQL job will still fail so that we alert the DBA to a failed backup.
You can download the new version here.
Tuesday, November 24, 2009
A few weeks ago, we were preparing to move our SQL Server 2005 databases from a 4-node Windows 2003 cluster to three 2-node Windows 2008 clusters. On the new clusters, we noticed that Database Mail and the monitoring application for Database Mirroring weren’t working on four of the SQL Server 2005 instances that we had installed. For the monitoring issue, we found that we were missing all of the sys.dbmmonitor* system stored procedures in the msdb database. We had installed six SQL Server 2005 instances across the six servers using the same media on each, so we were very perplexed as to why the issue was happening on four of the instances and not on all six. After doing our own research and coming up dry, we decided to open a case with Microsoft Product Support Services (PSS).
The Microsoft engineer assigned to our PSS case determined that the issue was due to five of the server names being in lower case. This caused the Resource database to not get patched when we installed service pack 3 and then October’s critical SQL hotfix (MS09-062). The Resource database was still at the RTM build, 1399, but it should have been at build 4262 like everything else.
When we first heard what the root cause was, our reaction was to laugh as this sounded absurd. I confirmed the bug with another Microsoft engineer just to be safe. Unfortunately there is no bug fix for it, but there is a workaround/solution which is documented here. The workaround involves evicting the nodes with lower case server names, upper casing the server names, and then joining the fixed node back into the cluster. After this is complete, you then reapply the service pack or hotfix. We only needed to reapply the hotfix.
The reason why we only encountered the issues on four of the six SQL Server instances that we installed is because on our third cluster one of the nodes had its server name in upper case. SQL Server 2005, service pack 3, and MS09-062 were installed from the server whose name was in upper case. Had we performed the installations on the other node with a lower case server name, we would have encountered the issue there too. We decided to perform the workaround on the working cluster since we could run into this issue again if we forgot to perform the SQL Server 2005 installations from the server with an upper case name. You do not need to perform the workaround on any servers that are properly named in all upper case.
Monday, November 09, 2009
You can only have one clustered index on each SQL Server table, however there are two ways to create pseudo clustered indexes on a table:
- Create clustered indexed on a view (indexed view) that covers the table
- Create covering index on the entire table
Let's look at the following table:
CREATE TABLE t1
(
c1 int,
c2 varchar(5),
c3 bigint,
c4 datetime,
CONSTRAINT PK_t1 PRIMARY KEY CLUSTERED (c1, c2, c3, c4)
)
GO
For option 1 above, here's the indexed view and clustered index:
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
CREATE VIEW v1 WITH SCHEMABINDING
AS
SELECT c1, c2, c3, c4 FROM dbo.t1
GO
CREATE UNIQUE CLUSTERED INDEX cidx_v1 ON dbo.v1(c2, c3, c4, c1)
GO
For option 2 above, here’s the covering index:
CREATE INDEX idx_PseudoClust ON t1(c3, c4) INCLUDE (c2, c1)
GO
So why would you want to do this? I'm sure that there are some very good reasons, but I've never had the need to do it. I recently had the discussion with a Microsoft engineer and thought it was interesting enough to share.
Tuesday, November 03, 2009
I have modified isp_ALTER_INDEX, which is the stored procedure that I wrote and use to defragment/rebuild indexes in SQL Server 2005 and SQL Server 2008. The code change has just one bug fix. The bug would only have been encountered on SQL Server 2008 systems that have XML indexes that need to be rebuilt based upon the input parameters.
In SQL Server 2005, the sys.dm_db_index_physical_stats data management function outputted “XML INDEX” for the index_type_desc column when the index contained a column with the XML data type. In SQL Server 2008, the DMF was modified so that you could have either “XML INDEX” or “PRIMARY XML INDEX” for XML indexes in the index_type_desc column.
I don’t have any systems that use the XML data type, so it wasn’t until we had the SQL Server 2008 AdventureWorks database on a development system that we found the bug.
Here’s the code change in case you are interested:
SET @online =
CASE
WHEN @indexType IN ('XML INDEX', 'PRIMARY XML INDEX') THEN 0
WHEN @indexType = 'NONCLUSTERED INDEX' AND @allocUnitType = 'LOB_DATA' THEN 0
WHEN @lobData = 1 THEN 0
WHEN @disabled = 1 THEN 0
WHEN @partitionCount > 1 THEN 0
ELSE 1
END
You can download the new version of the stored procedure
here.
Thursday, October 08, 2009
My Database Growth Tracker tool is featured in a SQL Server Magazine web article that Kevin Kline wrote.
Check out the web article here!
Monday, September 21, 2009
After attaching or restoring a database to SQL Server 2005, you may encounter an error when executing a CLR object.
Error:
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65538. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE.
This KB article describes the issue and shows the fix, however it says the bug occurs when the database is attached/restored to a different SQL instance. I’ve encountered the error three times now. In at least one occurrence of the error, it has happened when I’ve restored the database to the same SQL instance.
Fix:
USE master
GO
ALTER DATABASE dbNameGoesHere SET TRUSTWORTHY ON
USE dbNameGoesHere
GO
EXEC sp_changedbowner 'sa'
Thursday, September 10, 2009
The SQLCAT team released a very informative whitepaper regarding backup and recovery of very large databases in SQL Server 2008. It is a must read if you are a SQL Server DBA. Check it out here.
If you are not already following the SQLCAT team’s blogs and you are a SQL Server DBA, you need to get with the times and start following one of the best online resources for SQL Server.
Tuesday, September 08, 2009
I have modified isp_Backup, which is the stored procedure that I use to backup SQL Server databases. This new version includes one bug fix and three new features:
- Bug Fix – removed ReportServerTempdb from exclusion list
- Bug Fix – fixed file retention code to handle database names with spaces
- Feature – support for SQL Server 2008 including compression
- Feature – archive bit option
- Feature – COPY_ONLY option
I decided that excluding the ReportServerTempdb from the backups was unnecessary. This database, used by Reporting Services, is in FULL recovery model by default, so this exclusion could have caused you to run out of space in its transaction log.
Bryan Conlon, one of my blog readers, noticed that the file retention code was not working when a database name had spaces in it. It took me a while to come up with a solution for this one, but I came up with a solution that involves an additional temporary table that stores just the file names and then synchronizes with the original temporary table that handles the dates. If you are wondering why I don’t just use the FORFILES command along with the DEL command which probably wouldn’t have this bug, it is because you can’t use UNC paths with FORFILES. I didn’t want to limit storage of backups to local or mapped drives. To see a file retention solution that uses FORFILES with DEL, check out this blog.
The archive bit option was added in case you don’t want to delete files that haven’t been backed up to tape. If you aren’t backing up your files to tape, then do not use this feature unless you are setting this option to true on the files. The default value of this new input parameter is false, so you don’t need to do anything if you don’t want to use it.
The COPY_ONLY option was added due to a feature request from one of my blog readers, “The Baking DBA”. This option allows you to perform a backup without impacting the normal sequence of backups. For more details on this, please check out the COPY_ONLY option in the BACKUP DATABASE topic in SQL Server Books Online.
I also dropped support for SQL Server 2000, so this new version only supports SQL Server 2005 and SQL Server 2008. I decided to drop SQL Server 2000 support from this new version, and all future versions, as keeping it in there was making it harder to release new versions. If you still need SQL Server 2000 support, like I do on a legacy system, then do not deploy this new version to those systems, simply use the last version.
You can download the new version here.
Thank you to Bryan Conlon for also testing and finding bugs in the “beta” version of the stored procedure. All bugs that were found have been fixed. Thanks Bryan!
Wednesday, September 02, 2009
For various reasons, you may need to manually remove SQL Server replication. Microsoft has a good KB article which shows how to do it. It has always worked in the past, but I ran into a situation a couple of weeks ago where it couldn’t remove the distributor via sp_dropdistributor. I don’t recall what error we were getting, but I didn’t have time to figure out what was wrong so with the help of a Microsoft engineer we were able to setup another distributor and then get replication working again. Later I tried “drop database” command on the original distribution database, but it said it couldn’t be dropped since it was being used for replication. It no longer was being used for replication, so it seemed to be orphaned.
Yesterday, I opened a case with Microsoft to see how I could get rid of the orphaned distribution database. It’s a simple fix:
use master
go
alter database distribution set offline;
drop database distribution;