March 12, 2010 at 8:01 am
On the table I have a singe NC index on BatchExecutionID with no included columns. The field type is Nvarchar(50) and stores GUIDs.
The following query runs for over a minute doing a table scan.
For some reason it does not use the index. How can I get this to use an index without having to specify the With Index clause?
DECLARE @execid NVARCHAR(50)
SET @execid = '{00000000-0000-0000-0000-000000000000}'
SELECT [MMNUM]
,[MMLIN]
,[MMDES]
,[MMUSR]
,[MMMM]
,[MMDD]
,[MMYY]
,[MMCC]
,[MMUSRU]
,[MMMMU]
,[MMDDU]
,[MMYYU]
,[MMCCU]
,[MMCHRU]
,[MNCMNU]
,[DataQualityWarning]
,[DataQualityFailed]
,[DataQualityMessage]
,[RowID]
FROM [dq].[TableX]
WHERE BatchExecutionID = @execid
This version, which has the where clause hard coded, runs in under 1 sec:
SELECT [MMNUM]
,[MMLIN]
,[MMDES]
,[MMUSR]
,[MMMM]
,[MMDD]
,[MMYY]
,[MMCC]
,[MMUSRU]
,[MMMMU]
,[MMDDU]
,[MMYYU]
,[MMCCU]
,[MMCHRU]
,[MNCMNU]
,[DataQualityWarning]
,[DataQualityFailed]
,[DataQualityMessage]
,[RowID]
FROM [dq].[TableX]
WHERE BatchExecutionID = '{00000000-0000-0000-0000-000000000000}'
March 12, 2010 at 11:01 am
That's a classic case of parameter sniffing. SQL Server is able to use the hard coded value to determine that the index has what it needs and is relatively low cost, so it does a seek on the index and then either a RID or Key lookup to retrieve the data. When it uses the parameter, instead of having a precise value, it assumes a more generic term and checks the statistics of the indexes available, either assuming that the index isn't workable, or that it needs to scan the index.
Have you checked the execution plans? I'm sure they're different.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 12, 2010 at 11:31 am
Because that's a variable in the first script (not a parameter), SQL can't tell what they value of it is at compile time and it has to make a guess as to the number of rows returned. It often guesses badly.
http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/
btw, why are you storing GUIDs in an nvarchar column? Ignoring that they can't have unicode characters in, what's wrong with the uniqueidenifier type?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2010 at 8:20 pm
Grant Yes they are different the one using the variable is doing a table scan the other an index seek and yes a RID lookup. Don't understand why it can't evaluate that. This is actually being called from an SSIS package using SSIS parameters the result is the same in ssis if I hard code it runs in under a second with the parm its over a minute.
March 12, 2010 at 8:33 pm
Gail,
I checked out the article when I think back to the profiler output (don't have it in front of me) but it appeared that it may have created a temporary procedure then on the next line it called it passing in the guid. That being the case it should have been able to determine the appropriate exec plan.
I used the 2 sql examples above since these were returning the same results I saw in SSIS.
Also the guids were coming from an nvarchar at the source and I left them as is. will the guid notation with the curly braces implicitly convert back to the uniqueidentifier? and would I get better performance perhaps correct this issue I'm having?
thanks
March 13, 2010 at 12:30 am
Tom Van Harpen (3/12/2010)
I checked out the article when I think back to the profiler output (don't have it in front of me) but it appeared that it may have created a temporary procedure then on the next line it called it passing in the guid. That being the case it should have been able to determine the appropriate exec plan.I used the 2 sql examples above since these were returning the same results I saw in SSIS.
Post what you saw from profiler them. What you have posted here suffers from parameter sniffing (or more accurately the lack thereof). If the real situation is something different, then there's no way we can offer useful advice without seeing what you're details
Also the guids were coming from an nvarchar at the source and I left them as is. will the guid notation with the curly braces implicitly convert back to the uniqueidentifier? and would I get better performance perhaps correct this issue I'm having?
Should convert OK. Test and check
As for performance, it probably won't fix this issue, though, as I mentioned above, without seeing exactly what you're doing, it's hard to identify the exact issue.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 13, 2010 at 1:51 am
Tom,
Please read, run, and make sure you understand each point in this demonstration script:
Setup:
IF OBJECT_ID(N'tempdb..#TableX', N'U')
IS NOT NULL
DROP TABLE #TableX;
GO
-- Test table, with GUID column correctly typed
-- UNIQUEIDENTIFIER = 16 bytes
-- NVARCHAR(50) = 100 bytes
CREATE TABLE #TableX
(
row_id INTEGER IDENTITY NOT NULL PRIMARY KEY,
batch_execution_id UNIQUEIDENTIFIER NOT NULL,
other_column_padding CHAR(1024) NOT NULL DEFAULT (SPACE(1024))
);
GO
-- 50,000 random GUIDs
INSERT #TableX WITH (TABLOCKX)
(batch_execution_id)
SELECT TOP (50000)
NEWID()
FROM master.sys.columns C1,
master.sys.columns C2,
master.sys.columns C3;
GO
-- One particular GUID we will select out later
INSERT #TableX (batch_execution_id)
VALUES ({guid '99641F25-0FA8-4651-819B-E2DEC05D5113'});
GO
-- 50,000 more GUIDs
INSERT #TableX WITH (TABLOCKX)
(batch_execution_id)
SELECT TOP (50000)
NEWID()
FROM master.sys.columns C1,
master.sys.columns C2,
master.sys.columns C3;
GO
-- Unique index on the GUID *only*
CREATE UNIQUE NONCLUSTERED INDEX [UQ #TableX batch_execution_id]
ON #TableX (batch_execution_id)
WITH
(
FILLFACTOR = 100,
MAXDOP = 1,
ONLINE = OFF,
SORT_IN_TEMPDB = ON
);
GO
Demo:
-- Select a wide row from an explicit GUID
-- Uses an index seek
SELECT batch_execution_id,
other_column_padding
FROM #TableX
WHERE batch_execution_id = {guid '99641F25-0FA8-4651-819B-E2DEC05D5113'};
GO
-- Same SELECT, but using a variable
DECLARE @ExecId UNIQUEIDENTIFIER;
-- Note the following are both correct
SET @ExecId = CONVERT(UNIQUEIDENTIFIER, '99641F25-0FA8-4651-819B-E2DEC05D5113');
-- Notice the *exact* syntax here
SET @ExecId = {guid '99641F25-0FA8-4651-819B-E2DEC05D5113'};
-- Also uses an index seek
SELECT batch_execution_id,
other_column_padding
FROM #TableX
WHERE batch_execution_id = @ExecId;
GO
DROP TABLE #TableX;
GUIDs can be a powerful tool, but they must be used correctly.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 16, 2010 at 11:41 am
Sorry for the delayed response here is the profiler output for RPC Complete and TSQL Batch Complete. The commands are being called from an SSIS OLE Source component.
SET FMTONLY ON
select
BatchExecutionID
from
[dq].[TableX]
where
1 = 2
SET FMTONLY OFF
go
declare @p1 int
set @p1 = 1
exec sp_prepare @p1 output, N'@P1 nvarchar(50)', N'SELECT [MMNUM]
,[MMLIN]
,[MMDES]
,[MMUSR]
,[MMMM]
,[MMDD]
,[MMYY]
,[MMCC]
,[MMUSRU]
,[MMMMU]
,[MMDDU]
,[MMYYU]
,[MMCCU]
,[MMCHRU]
,[MNCMNU]
,[DataQualityWarning]
,[DataQualityFailed]
,[DataQualityMessage]
,[RowID]
FROM [dq].[TableX]
WHERE BatchExecutionID = @P1', 1
select
@p1
go
exec sp_execute 1, N'{00000000-0000-0000-0000-000000000000}'
go
exec sp_unprepare 1
go
March 16, 2010 at 1:25 pm
That should parameterise fine, which means possibly a different version of parameter sniffing.
Can you get an execution plan of that please? You can capture that with Profiler, it's the ShowPlan XML event.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 17, 2010 at 5:20 am
SELECT batch_execution_id,
other_column_padding
FROM #TableX
WHERE batch_execution_id = '99641F25-0FA8-4651-819B-E2DEC05D5113'
GO
-- Same SELECT, but using a variable
DECLARE @execid NVARCHAR(50)
SET @execid = '99641F25-0FA8-4651-819B-E2DEC05D5113'
-- Notice the *exact* syntax here
--SET @ExecId = {guid '99641F25-0FA8-4651-819B-E2DEC05D5113'};
-- Also uses an index seek
SELECT batch_execution_id,
other_column_padding
FROM #TableX
WHERE batch_execution_id = @ExecId; with above code , i found similar exec plan :w00t:
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 17, 2010 at 6:10 am
I still think your problems would diminish, if not disappear entirely, if you just use the correct data type.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 17, 2010 at 7:57 am
Ok here is the exec plan using the ShowPlan xml in Profiler. This is a very handy event to use.
I stripped out the query batches that SSIS runs to get meta-data info.
The attached plan immediately followed the prepare statement, the line following the showplan was the the execute statement.
March 17, 2010 at 9:06 am
That just shows a table scan. It's not using any index at all, estimating one row only. Looks like it's only a partial plan too, not sure why, so lots of useful info not there.
Sorry, I gave wrong trace event. Showplan is estimated. It's Statistics XML that's the actual plan. Could you perhaps get the Statistics XML plan and post that?
Have you posted anywhere the definition of the table and all indexes on it? If not, could you please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 17, 2010 at 9:16 am
Table Definition:
CREATE TABLE [dq].[TableX]
(
[MMNUM] [int] NULL
,[MMLIN] [int] NULL
,[MMDES] [nvarchar](70) NULL
,[MMUSR] [nvarchar](10) NULL
,[MMMM] [int] NULL
,[MMDD] [int] NULL
,[MMYY] [int] NULL
,[MMCC] [int] NULL
,[MMUSRU] [nvarchar](10) NULL
,[MMMMU] [int] NULL
,[MMDDU] [int] NULL
,[MMYYU] [int] NULL
,[MMCCU] [int] NULL
,[MMCHRU] [int] NULL
,[MNCMNU] [int] NULL
,[IsNewMember] [bit] NOT NULL
CONSTRAINT [DF_memo_IsNewDim] DEFAULT (CONVERT([bit], (0), (0)))
,[IsType1] [bit] NOT NULL
CONSTRAINT [DF_memo_IsType1] DEFAULT (CONVERT([bit], (0), (0)))
,[IsType2] [bit] NOT NULL
CONSTRAINT [DF_memo_IsType2] DEFAULT (CONVERT([bit], (0), (0)))
,[IsInferredMember] [bit] NOT NULL
CONSTRAINT [DF_memo_IsInferredMember] DEFAULT (CONVERT([bit], (0), (0)))
,[NoChange] [bit] NOT NULL
CONSTRAINT [DF_memo_NoChange] DEFAULT (CONVERT([bit], (0), (0)))
,[DataQualityWarning] [bit] NOT NULL
CONSTRAINT [DF_memo_DataQualityWarning] DEFAULT (CONVERT([bit], (0), (0)))
,[DataQualityFailed] [bit] NOT NULL
CONSTRAINT [DF_memo_DataQualityFailed] DEFAULT (CONVERT([bit], (0), (0)))
,[DataQualityMessage] [varchar](1000) NULL
,[RowTransfered] [bit] NOT NULL
CONSTRAINT [DF_memo_RowTransfered] DEFAULT (CONVERT([bit], (0), (0)))
,[RowID] [uniqueidentifier] NOT NULL
CONSTRAINT [DF_memo_RowID] DEFAULT (newid())
,[CreateTimestamp] [datetime] NOT NULL
CONSTRAINT [DF_memo_RecordInserted] DEFAULT (getdate())
,[SourceSystemDescription] [varchar](200) NULL
,[SourceSystemKey] [nvarchar](200) NULL
,[BatchExecutionID] [nvarchar](50) NOT NULL
,[MasterExecutionID] [nvarchar](50) NULL
)
ON [PRIMARY]
Single index on Batch Exec ID:
CREATE NONCLUSTERED INDEX [idx_BatchExecID] ON [dq].[TableX] ([BatchExecutionID] ASC)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
March 17, 2010 at 9:34 am
Edited..
Is that the only index on the table? No primary key?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 50 total)
You must be logged in to reply to this topic. Login to reply