1

We have a database with about 50-60 % recompiles. That value comes from [SQL Compilations/sec] coupled with [Batch Requests/sec].

We Think that that value is a bit high

If we look at this query:

 SELECT TOP 150
    qs.plan_generation_num,
    qs.execution_count,
    qs.statement_start_offset,
    qs.statement_end_offset,
    st.text
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    WHERE qs.plan_generation_num > 1
    ORDER BY plan_generation_num DESC

We don't have a lot of plan_generation_num if you compare it to execution count. Wat we do have is a lot of single use objects and I am trying to figure out why?

Our application is built in ASP.NET and we always use parameterized querys. We use both SP's and SQL-statements in the application but always parameterized.

The webpage that runs agains this database is a pretty big website with about 500 000 pageviews each day and about 10 000 request per minute if this information helps.

We have no long running Querys and indexes and statisics are in order. This is one of the last things to optimize.

CPU is average 15% ram is about 100 gb and of coursed used up by SQL-server. We use SQL Server 2014 Enterprise.

One thing I started wondering about. If I have a sql statement like this

SELECT doors, windows, seats from cars where Wheels = @Wheels AND Active = 1

Will this plan not be reused beacause we don't set a parameter on this part: **AND Active = 1 **

Any idea on how to get an idea on why we have so much single use? The Count on cached plans is about 20 000. In comparasion we have about 700 sp' and a lot more querys in the app.

bummi
  • 27,123
  • 14
  • 62
  • 101
Addeladde
  • 777
  • 2
  • 9
  • 28
  • 1
    Do you use `.AddWithValue` with string arguments in your code? That will create NVarChar parameters based on string length. Each parameter length will create unique plan. Multiple parameters means exponential number of plans. – adrianm Oct 18 '14 at 15:53
  • We usually add parameters like this cmd.Parameters.Add(new SqlParameter("@parameter", parameter)); – Addeladde Oct 19 '14 at 12:40
  • 1
    Same problem. You don't specify the database type nor length so it is infered from the type of your `parameter` variable. If `parameter` contains "ABC", `@parameter` will be NVarChar(3). If it contains "ABCD" it will be NVarChar(4) etc. These will have separate plans in the cache. – adrianm Oct 19 '14 at 12:52
  • Ok, thanks we will have to go through our code to change this. Thanks – Addeladde Oct 19 '14 at 12:53
  • Don't forget to review your string length error handling on insert/update. If your DB type is NVarChar(3) then new SqlParameter("@parameter", "ABCD") will give a "string will be truncated" error while specifying parameter length will silently truncate the string. If you still want the error you should specifiy the parameter length longer than the database column. (e.g. column length +5 to make room for a few spaces) – adrianm Oct 19 '14 at 13:18

0 Answers0