February 21, 2011 at 8:21 am
Hello
I'm work now on tuning old web-app which use ORMapper by Paul Wilson.
ORMapper use dynamic queries to get data from SQL and I noticed many many sql plans which are almost the same except parameter.
I.e.
SELECT [SSNHistory].[SSNHistoryID], [SSNHistory].[IndID], [SSNHistory].[SSN], [SSNHistory].[CreateDate], [SSNHistory].[UpdateDate], [SSNHistory].[UpdateUserID], [SSNHistory].[SentDateToLoanTravel], [SSNHistory].[IsDeleted] FROM [SSNHistory] WHERE (IndID='xxxxxxx-3349-45e0-92ce-94ea8f861bf6');
SELECT [SSNHistory].[SSNHistoryID], [SSNHistory].[IndID], [SSNHistory].[SSN], [SSNHistory].[CreateDate], [SSNHistory].[UpdateDate], [SSNHistory].[UpdateUserID], [SSNHistory].[SentDateToLoanTravel], [SSNHistory].[IsDeleted] FROM [SSNHistory] WHERE (IndID='xxxxxxx-3349-45e0-92ce-94ea8f861bf7');
I did some research and found what auto parameterization work only when where condition use PK field, i.e.
SELECT [SSNHistory].[SSNHistoryID], [SSNHistory].[IndID], [SSNHistory].[SSN], [SSNHistory].[CreateDate], [SSNHistory].[UpdateDate], [SSNHistory].[UpdateUserID], [SSNHistory].[SentDateToLoanTravel], [SSNHistory].[IsDeleted] FROM [SSNHistory] WHERE [SSNHistory].[SSNHistoryID] = 5
and related plan is
(@1 tinyint)SELECT [SSNHistory].[SSNHistoryID],[SSNHistory].[IndID],[SSNHistory].[SSN],[SSNHistory].[CreateDate],[SSNHistory].[UpdateDate],[SSNHistory].[UpdateUserID],[SSNHistory].[SentDateToLoanTravel],[SSNHistory].[IsDeleted] FROM [SSNHistory] WHERE [SSNHistory].[SSNHistoryID]=@1
I tried Parameterization to be set to Forced - everything work in the same way, only PK.
Can you point me where is my mistake or why auto parameterization work only with PK?
Thanks,
Alex.
February 21, 2011 at 11:07 am
Forced Parameterization has a huge number of possible exceptions. If this code is using prepared statements, then that is one of the exceptions. Here's the list.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 21, 2011 at 11:25 am
I posted exact SQL query which can not be auto parameterized. It's not use anything restricted.
I found another info here
http://technet.microsoft.com/en-us/library/cc966425.aspx
SQL Server's LPE (Language Processing and Execution) component auto-parameterizes SQL statements. When QP (query processor) component realizes that values of literal constants does not affect query plan choice, it declares LPE's attempt of auto-parameterization "safe" and auto-parameterization proceeds; otherwise, auto-parameterization is declared "unsafe" and is aborted. Values of some of the perfmon counters mentioned in Section 11.5 ('SQLServer: SQL Statistics' category) report statistical information on auto-parameterization.
I not sure what this mean, because both queries with different guids in the Where clause generate similar query plan (only difference in the constant).
Alex.
February 21, 2011 at 12:28 pm
A similar query plan is not the same thing as the same query plan.
Regardless, I suspect you're hitting one of the exceptions, it's just not clear which one. You're seeing why many of the ORM tools that are out there are so problematic.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 22, 2011 at 1:17 am
Plans are exactly the same, only difference in CompileTime, CompileCPU, and GUID parameter, both plans attached.
Is it possible there some other options on the database level which I missed or need to check?
Thanks, Alex.
February 23, 2011 at 12:50 pm
Grant Fritchey (2/21/2011)
. . . many of the ORM tools that are out there are so problematic.
Understatement of the week at least, perhaps the month! 😀
ORMs are BAD BAD BAD for database performance. I can extend that statement more generically: "almost everything that allows the developer to slap database application code together more quickly results in horrible database performance". 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 24, 2011 at 2:17 am
So no way to fix it with less blood?
I see only one way for now - replace most used requests to be in the Stored Procedure.
Alex.
February 24, 2011 at 2:34 am
onixsoft (2/24/2011)
So no way to fix it with less blood?
If you run the IndId= GUID query from Management Studio, do you get an auto-parameterized plan? I would expect that to be the case. If the lack of parameterization only occurs with UNIQUEIDENTIFIER columns, I would check which drivers (e.g. Native Client, SQLOLEDB, ODBC) the client was using to connect to SQL Server, and see if there is an update.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 24, 2011 at 2:55 am
SQLkiwi (2/24/2011)
onixsoft (2/24/2011)
So no way to fix it with less blood?If you run the IndId= GUID query from Management Studio, do you get an auto-parameterized plan? I would expect that to be the case. If the lack of parameterization only occurs with UNIQUEIDENTIFIER columns, I would check which drivers (e.g. Native Client, SQLOLEDB, ODBC) the client was using to connect to SQL Server, and see if there is an update.
No, by run in the Management Studio I do not get auto-parameterized plan also.
February 24, 2011 at 3:03 am
onixsoft (2/24/2011)
No, by run in the Management Studio I do not get auto-parameterized plan also.
The following script gives me an auto-parameterized query in both cases:
CREATE TABLE dbo.SSNhistory
(
SSNHistoryID INTEGER IDENTITY PRIMARY KEY,
IndID UNIQUEIDENTIFIER
DEFAULT NEWSEQUENTIALID()
ROWGUIDCOL NOT NULL
UNIQUE,
SSN CHAR(12) NOT NULL,
CreateDate DATETIME NOT NULL DEFAULT GETUTCDATE(),
UpdateUserID INTEGER NULL,
UpdateDate DATETIME NULL,
IsDeleted BIT NOT NULL DEFAULT 0,
SentDateToLoanTravel DATETIME NULL,
)
;
INSERT dbo.SSNhistory
(
IndID,
SSN
)
VALUES (
{GUID '00000000-9C9A-4213-AF88-00069446D83B'},
'Some SSN'
)
;
-- Prefix: [Sandpit].[dbo].[SSNhistory].IndID = Scalar Operator(CONVERT_IMPLICIT(uniqueidentifier,[@1],0))
SELECT *
FROM SSNhistory AS SH
WHERE SH.IndId = '00000000-9C9A-4213-AF88-00069446D83B'
;
-- Prefix: [Sandpit].[dbo].[SSNhistory].SSNHistoryID = Scalar Operator(CONVERT_IMPLICIT(int,[@1],0))
SELECT *
FROM SSNhistory AS SH
WHERE SH.SSNHistoryID = 1
;
I don't want to put you to any trouble, but if you could script a CREATE DATABASE statement for your database that might help highlight any differences there.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 24, 2011 at 4:30 am
Thanks Paul, I was able to recreate table structure and did some more research and found what following line turn autoparameterization off :
CREATE NONCLUSTERED INDEX [IDX_SSNHistory_IndID] ON [dbo].[SSNHistory] ([IndID]) ON [PRIMARY]
GO
Do you have any ideas why?
Thanks, Alex.
February 24, 2011 at 4:37 am
onixsoft (2/24/2011)
Thanks Paul, I was able to recreate table structure and did some more research and found what following line turn autoparameterization off :CREATE NONCLUSTERED INDEX [IDX_SSNHistory_IndID] ON [dbo].[SSNHistory] ([IndID]) ON [PRIMARY]
GO
Do you have any ideas why?
If I understand you correctly, you are saying that auto-param worked fine until you created that non-clustered index. Is that correct? Auto-param works for me with and without that index, and I can't see an immediate reason why it should be different for you. I suppose it might have something to do with SET options - try running this on your connection before creating the test table, index, and data:
SET ANSI_NULLS,
ANSI_PADDING,
ANSI_WARNINGS,
ARITHABORT,
CONCAT_NULL_YIELDS_NULL,
QUOTED_IDENTIFIER
ON;
SET NUMERIC_ROUNDABORT
OFF;
These are the recommended settings, by the way.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 24, 2011 at 5:37 am
If I understand you correctly, you are saying that auto-param worked fine until you created that non-clustered index. Is that correct? Auto-param works for me with and without that index, and I can't see an immediate reason why it should be different for you. I suppose it might have something to do with SET options - try running this on your connection before creating the test table, index, and data:
Yes you understood correctly.
This one code batch works fine :
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
drop TABLE dbo.SSNhistory1
CREATE TABLE dbo.SSNhistory1
(
SSNHistoryID INTEGER IDENTITY(1,1) PRIMARY KEY,
IndID UNIQUEIDENTIFIER,
SSN CHAR(12) NOT NULL,
CreateDate DATETIME NOT NULL DEFAULT GETUTCDATE(),
UpdateUserID INTEGER NULL,
UpdateDate DATETIME NULL,
IsDeleted BIT NOT NULL DEFAULT 0,
SentDateToLoanTravel DATETIME NULL,
)
;
INSERT dbo.SSNhistory1
(
IndID, SSN, CreateDate, UpdateUserId, UpdateDate
)
VALUES (
{GUID '00000000-9C9A-4213-AF88-00069446D83B'},
'Some SSN',
getdate(), 1, getdate()
)
;
--CREATE NONCLUSTERED INDEX [IDX_SSNHistory_IndID1] ON [dbo].[SSNHistory1] ([IndID]) ON [PRIMARY]
--GO
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
And auto-param works fine also for this query
SELECT *
FROM SSNhistory1 AS SH
WHERE SH.IndId = '00000000-9C9A-4213-AF88-00069446D83B'
;
Once I uncomment CREATE NONCLUSTERED INDEX , auto-param not work anymore.
Alex.
February 24, 2011 at 5:50 am
And some more info:
1.
DB Compatible Level: SQL Server 2000
Parametereization: Simple
With NONCLUSTERED INDEX
Auto-param not work
DB Compatible Level: SQL Server 2000
Parametereization: Forced
With NONCLUSTERED INDEX
Auto-param not work
2.
DB Compatible Level: SQL Server 2000
Parametereization: Simple
Without NONCLUSTERED INDEX
Auto-param work properly
DB Compatible Level: SQL Server 2000
Parametereization: Forced
Without NONCLUSTERED INDEX
Auto-param work properly
3.
DB Compatible Level: SQL Server 2005
Parametereization: Simple
With NONCLUSTERED INDEX
Auto-param not work
DB Compatible Level: SQL Server 2005
Parametereization: Forced
With NONCLUSTERED INDEX
Auto-param work properly
4.
DB Compatible Level: SQL Server 2005
Parametereization: Simple
Without NONCLUSTERED INDEX
Auto-param work properly
DB Compatible Level: SQL Server 2005
Parametereization: Forced
Without NONCLUSTERED INDEX
Auto-param work properly
Alex.
February 24, 2011 at 5:53 am
I think I have a handle on this now, but I just want to be sure you see a successful auto-param in all cases (index, no index) for this form of the query:
SELECT *
FROM SSNhistory1 AS SH
WHERE SH.IndId = '00000000-9C9A-4213-AF88-00069446D83B' + '';
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply