December 29, 2009 at 1:55 am
After tracing our database, many queries run with 'SP_EXECUTESQL' and run more than ten seconds, but I rerun these queries in SQL Server Management Studio and found only few queries run more than 2 seconds, so I don't know why and how to slove this prolem.
Did any defact for using 'SP_EXECUTESQL', please give me some suggest, thanks!
For example:
The query like this:
exec sp_executesql N'Select CN, CT, DI,BCT From RCDetails Where BRSID=@sid order by CN,N'@sid nvarchar(36)',@sid=N'b741e659-ed51-44d0-8c99-a8b7561a28cb'
Some infomation about this query:
Table 'RCDetails' data space: more than 2GB
Table 'RCDetails' data rows: more than 4,500,000
Column 'CN','CT','DI','BCT' Type: NVARCHAR(MAX)
Colunm 'BRSID' Type:uniqueidentifier
There is a nonclustered index on column BRSID, and this query will return about 20 records, I have traced that this query run more 10 seconds, but if I rerun it(with warm cache) and it used about 1 second, so I confused about 'sp_executesql'.
by the way, more than 90% query statements use 'sp_executesql' in my project.
December 29, 2009 at 2:53 am
Are you passing search arguments as parameters ?
If so you should be able to duplicate the poor performance using variables.
In any case you will need to post the execution plan for further diagnosis.
See the links in my sig below for details of how to do that.
December 29, 2009 at 3:37 am
Post the queries please plus the two execution plans, one from running it as a query, one from running it via sp_executesql. See http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
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
December 29, 2009 at 3:59 am
Dave Ballantyne (12/29/2009)
Are you passing search arguments as parameters ?If so you should be able to duplicate the poor performance using variables.
In any case you will need to post the execution plan for further diagnosis.
See the links in my sig below for details of how to do that.
]
Thanks for all your help!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply