« January 2008 | Main | March 2008 »
February 28, 2008
Good Resource for Diagnosing SQL Server Performance Issues
FYI. I don't know how many have seen this article in the January MSDN magazine, but it is a good resource for queries to help diagnose performance issues.http://msdn.microsoft.com/msdnmag/issues/08/01/SqlDmvs/default.aspx
you can also download the script that accompanies the article here:
http://msdn.microsoft.com/msdnmag/code/?url=http://msdn.microsoft.com/msdnmag/issues/08/01/SqlDmvs/default.aspx
I have recently had to use these scripts to investigate some performance problems with a production sql server. They proved to be very insightful.
Xavier D. Lazard,
Senior Software Engineer, BSEE, MCSD, MCDBA
RDA
Share:
Posted by Steve Mann at 11:51 AM | Comments (0)
February 26, 2008
Generate ShowPlanXML using Extended Stored Procedures.
Recently at a client site we were notified of a situation †where the same Stored Procedure †when executed from a VB.Net Application via OLEDB †would take more time to execute, about 1.5 minutes vis-à -vis when executed directly in Query Analyzer †when it took only 12 seconds. Unfortunately †this happened occasionally and not every time.Last time it happened †it was well after 6:00 PM and everybody had left, but the lead developer was informed about the situation and he logged in remotely to see what was going on †and he executed the SP from Query Analyzer and found different results. It baffled him and just to calm the situation down †he recompiled the SP and everything was back to normal. But we were sure the problem would come back to haunt us.
Solution was to find the differences in execution plan of the SP when run from the Application and compare it to the plan when run via the Query Analyzer †and see if there were any clues. Leaving the Profiler Running on a Production DB †especially since the problem was inconsistent was not an acceptable solution. So we developed a script which gives the execution plan of the SP whenever it is executed -- in a trace file, which could be examined later on.
This SP was created †in the master DB (You can create it in any DB you like).
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spGenerateTrace]
@spName SYSNAME,
@FolderName NVARCHAR(100),
@maxFileSize BIGINT
AS
/*
EXECUTE spGenerateTrace 'uspGetEmployeeManagers', 'C:\Test\', 400
*/
DECLARE @tracefile NVARCHAR(256),
@traceid INT,
@rc INT,
@FilePath NVARCHAR(500)
SET @tracefile = @FolderName @spName '_trace'
--SET @FilePath = @FolderName @spName
EXECUTE @rc = sp_trace_create @traceid OUTPUT, 0, @traceFile, @maxFileSize, NULL, 0
DECLARE @on BIT
SET @on = 1
-- Set the events you want to capture.
-- We are capturing here -- ShowPlanXML and SP:StmtStarting and SP:StmtCompleted events.
EXEC sp_trace_setevent @traceid, 44, 1, @on
EXEC sp_trace_setevent @traceid, 44, 14, @on
EXEC sp_trace_setevent @traceid, 45, 1, @on
EXEC sp_trace_setevent @traceid, 45, 14, @on
EXEC sp_trace_setevent @traceid, 122, 1, @on
EXEC sp_trace_setevent @traceid, 122, 14, @on
EXEC sp_trace_setevent @traceid, 122, 34, @on
-- We will filter the trace to capture events only when the SP that we want is executed.
EXEC sp_trace_setfilter @traceid, 34, 0, 0, @spName
--Start the trace.
EXEC sp_trace_setstatus @traceid, 1
SELECT @traceid
When you run this SP it will return the trace ID. It is very important to keep that number †safe and sound somewhere. Because you will have to pass that traceid to the next SP to stop the trace files when you are ready to do so.
You can also get all the traces running by executing the following statement
Select * from fn_trace_getinfo(0).
You should be able to identify your trace file and its ID from the above statement.
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spStopTrace]
@traceid INT
AS
/*
EXECUTE spStopTrace 2
*/
EXEC sp_trace_setstatus @traceid, 0
EXEC sp_trace_setstatus @traceid, 2
This helped us collect the Execution Plan information without having to keep an eye on the Profiler Data. And the next time the problem was reported we could track the execution plan that was generated at that time and compared it †with what was being generated when executed from the Query Analyzer and found siginificant differences, The problem was a malicious index (which had a lof of included columns).
There were two solutions to fix the problem †use a Plan Query Hint or Remove/Fix the malicious index. We opted not to use the Plan Query Hint because of the unintended consequences it might have †down the line. Removing the malicious index was perfectly acceptable to the user community.
PS: Once the size @maxfile Size (in MB) is reached, the trace output will not be collected any more unless you use the @filecount parameter †of sp_trace_create, which will generated multiple files till the @filecount number is reached.
Share:
Posted by Steve Mann at 3:41 PM | Comments (0)
SQL Server 2005 Consistency Based I/O Error
We had a client who reported a I/O checksum error on their SQL Server 2005 database. Below is the error message. (References to object names, database names and file names were slightly modified to protect the innocent)SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xc19dc879; actual: 0x64d83467). It occurred during a read of page (1:39159) in database ID 6 at offset 0x000000131ee000 in file 'U:\Microsoft SQL Server\Data_PROD\OurDatabaseName.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
As you can see this does recommend that we run CHECKDB which was done. We ran DBCC Checkdb and it reported the following:
DBCC checkdb:
DBCC results for 'OurDatabaseName'.
Msg 8928, Level 16, State 1, Line 1
Object ID 240055900, index ID 2, partition ID 72057594104512512, alloc unit ID 72057594108706816 (type In-row data): Page (1:39159) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 240055900, index ID 2, partition ID 72057594104512512, alloc unit ID 72057594108706816 (type In-row data), page (1:39159). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 46270473 and -4.
As you can see, checkdb does provide us with the object_id and index ID of the culprit. Given, the object_id we can get the table name as follows: select object_name(240055900)
Let's assume our table in question is dbo.sick_table
So, what were our steps to resolve this issue?
Last Resort Steps
We wanted to avoid running dbcc with the fix options especially those options that allow data loss -- so that was our last resort. (We have found that Ken Henderson's The Guru's Guide to SQL Server books provide all of the gorry details regarding DBCC options and internals) We also wanted to avoid restoring from a backup and losing working depending on the age of the most recent backup.
Possible non-invasive steps
Since our error message did reference an index, we though it would be wise just to drop the indexes and try recreating them. (the index id references could be found in the sys.indexes table) We displayed the indexes for our table using the following command:
exec sp_helpindex 'dbo.sick_table'
Indexes could be dropped and then recreated either from Management Studio's GUI or by using the Transact-SQL statements. It turned out that dropping and recreating the indexes did fix our problem!
Lesson Learned
Review the error messages carefully. Think of non-invasive steps such as dropping and rebuilding indexes especially if the errors reported specify indexe by an index_id. We also ran dbcc checkdb AFTER dropping and adding the indexes and there were no longer any errors reported.
Share:
Posted by Steve Mann at 12:41 PM | Comments (0)
February 22, 2008
Report Viewer Web part in SQL Server 2005 Reporting Services: "Object reference not set to an instance of an object"
There is a known issue with the Report Viewer web part using parameterized reports. The issue is described here: http://support.microsoft.com/default.aspx/kb/942530.However, we ran into some cases where the cumulative patches did not resolve the issue. Here are the results from one of our project teams:
After a grueling 3.5 hour call with Microsoft support, we finally fixed the problem. The gory details are as follows.
First, the hot fix mentioned in the KB article was never included in Cumulative Patch 4 or 5. Cumulative Patch 6 was released today. However, the support group is still researching whether the hot fix was included in it or not. They will get back to me. To install the hot fix I had to uninstall Cumulative Patch 5 and then 4 from the Reporting Services box. Then looking at the hot fix, I realized that it was simply a newer version of “SQL Server Reporting Services Add-In for SharePoint Technologies”. So that perhaps explains why it wasn’t included in the Cumulative Patches 4 and 5. However, the support person told me NOT to install Cumulative Patch 4 or 5 over this hot fix as they contain versions of DLLs that may overwrite this patch. Again, they will get back to me whether this is resolved in Cumulative Patch 6 or not.
Secondly, this hot fix is required to be installed on all the SharePoint servers too to resolve the issue. Again, there is no mention of this in the KB article. Then when I tried to install the hot fix on our 4 SharePoint servers, it went thru fine on 1 server but “failed” on 3 of them. So to troubleshoot the issue, we decided to uninstall the original “SQL Server Reporting Services Add-In for SharePoint Technologies” and simply install the newer version. However after uninstalling the original version, we were unable to install the upgrade OR the original version leaving our SharePoint installation broken! Then after doing some research, the support person found the following method (http://msdn2.microsoft.com/en-us/library/aa905871(SQL.100).aspx) to run the install from the command prompt:
1. Run the original setup as follows: msiexec /I SharePointRS.msi SkipCA = 1
2. Navigate to the %TEMP% folder and run the following: rsCustomAction.exe /i
Please NOTE that the verbose messages at the end of the 2nd command will say that the install failed. But it actually goes thru!
After doing the above on 1 server, we realized that there was really no need to uninstall everything and re-install because even though the upgrade said it “failed”, it actually went thru. So we did some testing on our remaining 2 “failed” servers and they were actually fine. To make sure the problem was really truly fixed, I restarted all the SharePoint WFE servers in NLB, turned IIS off on each of them except 1 and hit the web-site to ensure the problem didn’t occur.
Share:
Posted by Steve Mann at 9:11 PM | Comments (0)
Importing Excel Files with SSIS and Getting NULLs
If you’ve had issues with importing Excel files in SSIS, this may be helpful.http://technet.microsoft.com/en-us/library/ms141683.aspx
The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data type of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values for cells that contain data of the other type. (In a tie, the numeric type wins.) Most cell formatting options in the Excel worksheet do not seem to affect this data type determination. You can modify this behavior of the Excel driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window.
For example:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\Book1.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1";
With IMEX=1 columns are automatically imported as Unicode string [DT_WSTR](255). You’ll have to convert to numeric if necessary but it’s better than guessing numeric and anything that isn’t winds up being null.
Share:
Posted by Steve Mann at 9:01 PM | Comments (0)
