[MS SQL Server] SQL Server TDE Encryption |
- SQL Server TDE Encryption
- SQL 2008 R2 High Memory Usage
- sp_executesql -- can produce very wrong execution plan
| Posted: 09 Jun 2013 11:12 PM PDT Hi All,I have a PRODDB which is encrypted with TDE .I have DMK and DBCert Key with me .I also have the password for the keys .Can you please help and suggest the Steps I should use to change the password for Keys ?? |
| Posted: 09 Jun 2013 08:08 PM PDT Dears, is it normal when sqlservr.exe takes that much of memory usage ?The installed is Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) over Windows 2008 R2 SP1 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)Please check the attached screen shot and let me know if further information is needed.Thanks all and really I appreciate your fast response as this is a production DB. |
| sp_executesql -- can produce very wrong execution plan Posted: 09 Jun 2013 11:56 AM PDT A front end app sends a query to SQL 2008 using sp_executesql.The query returns a count as the final result.The query hits some large tables (2m - 3m rows)The query is very poorly constructed (one of those SQL generators) consisting of many joins, cross DB joins (same server), sub queries, correlated sub queries etc etc.Five parameters are passed via sp_executesql, with one of these values the most likely to change and affect the size of the subset of data.The query normally takes a couple of seconds to run with the average number of rows in the first part of the query plan being 3000 - 5000.Every now and then the query takes 15 - 20 minutes.The only change is the one parameter (that mainly affects the subset of data)In the cases we know of this change takes the subset to 50,000 - 60000 records when the slow performance happens.Even when the same query is run via sp_executesql in SSMS it normally only takes seconds.So having said that..I got some info the other day when the query was slow and the execution plan it used was [b]WAY [/b]different.It was not a case of the same plan that was not optimal for the slightly larger record set but a totally different plan.If I run SP_UPDATESTATS it fixes the issue -- I assume it forces a plan recompile so only indirectly fixes the issue??Comparing the same statement using sp_executesql , one good and one bad I noticed the following from the Plan XML:Remember exactly the same query run in exactly the same way except one must have had a new plan created via the update stats.good: (1 second)EstimateCPU="0.000756482" ActualRows="3608" -- from the first filter step in the plan<ColumnReference Column="@P2" ParameterCompiledValue="(3817)" ParameterRuntimeValue="(3817)" /> -- 3817 is the value passed inbad: (17 min) EstimateCPU="3.91782E-05" ActualRows="2277436" from the first filter step in the plan<ColumnReference Column="@P2" ParameterCompiledValue="(3969)" ParameterRuntimeValue="(3817)" /> -- 3817 is the value passed inSo I guess the difference in performance is for the same step (JOIN) in the plan the good one returns 3608 rows the other one returns 2.2 million rows.and it is SEEKing and doing Key Lookups with these 2.2 million rows.Is that enough info for somebody to help me understand why it does this and how to fix it?You can also see the bad query is re-using a previous plan because the ParameterCompiledValue and ParameterRuntimeValue values are different?I thought parameter sniffing with sp_executesql would use the same plan for each parameter which may cause a SEEK instead of a SCAN rather than a totally different plan?thanks |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 Administration To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment