![](3D"KB703_SQLServer2000P&TSurvivalGuide_files/image002.gif")
SQL Server Development Customer Advisory Team
P&T Survival Guide
Microsoft Corporation
October 2003
Writer:
Copyright
The information contained in this document represents the curr= ent view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, = it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after = the date of publication.
This white paper is for informational purposes only. MICROSOFT= MAKES NO WARRANTIES, EXPRESS OR IMPLIED, AS TO THE INFORMATION IN THIS DOCUMENT.<= /p>
Complying with all applicable copyright laws is the responsibi= lity of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system= , or transmitted in any form or by any means (electronic, mechanical, photocopyi= ng, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any licen= se to these patents, trademarks, copyrights, or other intellectual property.
Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place or event is intended or should be inferred.
Ó 2003 Micros= oft Corporation. All rights reserved.
Microsoft and Windows are registered trademarks of Microsoft
Corporation in the
The names of actual companies and products mentioned herein ma= y be the trademarks of their respective owners.
Query =
Plans
Compil=
ed
Plan
Execut=
ion
Plan
Plan r=
e-use
Syscac=
heobjects
Auto-p=
arameterization
Sp_exe=
cutesql
Syscac=
heobjects
(re-use=3Duscounts>1)
Analyz=
ing
Plan re-use
Sample
output from sp_analyze_executable_plans.
Tracki=
ng
recompilation in a Profiler trace..
EventS=
ubClass
Writing
recompilation info in the errorlog..
Sample
Errorlog recompilation entries
Statis=
tical
(rows changed) thresholds
Avoiding
re-comps based on rows changed (EventSubClass=3D2)
Stored
Procedure Design issues with temporary tables (EventSubClass=3D5)=
Set Op=
tions
Set
options causing recompilation (eventsubclass=3D4)
Perfmon
counters for SQL compilation =
and
re-compilation
Profil=
er
events for plan re-use, compilation & re-compilation..
Memory
limits for 32-bit SQL 2000
Determining
what is in real memory cache
Syspro=
cesses
DBCC
SQLPERF(waitstats)
Track_=
waitstats
Open
Transactions, Locks & Blocking..
Syspro=
cesses
Dbcc
inputbuffer(spid)
::fn_g=
et_sql
Sysloc=
kinfo
Sp_blo=
ckinfo
Perfor=
mance
Counters
Sysper=
finfo
Purpose of Survival Guide<=
/span>
This guide contains background and details=
on
common performance issues such as optimization (compilation), query plans, =
plan
re-use, recompilation, blocking, waitstats, and performance monitor (PERFMO=
N)
counters.
A good methodology for solving performance
problems can be found in the document “SQL Server Performance using W=
aits
& Queues.doc”.
Optimization
Optimization is the processes of identifyi=
ng
and selecting the best strategy of data retrieval out of a number of possib=
le
alternate strategies. This pr=
ocess
takes place prior to execution and retrieval of results. The output of optimization, a quer=
y plan,
executes to return the query results.
Query Plans<= /a>
SQL Server 7 and 2000 st=
ored
procedure query plans are broken into two parts: Executable and compiled plans. Compiled plans are sharable =
by
many users whereas executable plans are not. Query plans are stored in the virt=
ual
system table master..syscacheobjects.
Compiled Plan
A compiled plan is a
reentrant, read-only data structure used by any number of users. No user
context is stored in the compiled plan. There are never more than one or tw=
o copies
of the query plan in syscacheobjects: one copy for all serial executions and
possibly another for all parallel executions. The parallel copy covers all
parallel executions, regardless of their degree of parallelism.
Execution Plan
The
executable plan is a user specific runtime context e.g. a data structure th=
at
holds the data specific to their execution, such as parameter and variable =
values.
This data structure is called the execution context or execution plan.
The
execution context data structures are re-usable but not re-entrant. Consequently, when multiple users
execute a stored procedure concurrently, each user will be given their own
execution plan. Thus, multiple
executable plans may exist, especially in high concurrency environments.
SQL Server 2000 Plan re-use =
Prior to execution of a SQL statement, a q=
uery
plan is required to determine the best access strategy that will used to
retrieve data. The query plan=
is
what executes to return the actual result set.
OLTP work loads are typically comprised of
many similar small transactions. &nbs=
p;
These high volumes of like transactions should also have a high numb=
er
of equivalent query plans. Si=
nce
plan determination, also known as optimization, is a CPU intensive process =
that
can extend the duration of the transaction during optimization, it is often
desirable to re-use plans especially in an OLTP environment, rather than
recomputed them for arguably identical query plans.
Certainly plan re-use is not always a good thing. For example, sometimes= the size of query result sets (and interim results/tables) vary greatly dependi= ng on the where clause. For example, if one query retrieves order for a customer number, while another user retrieves all orders for all customers, the access strategies should be different. In the first case, you’d wan= t to use an index to go straight to that customer’s orders. In the second case, a table scan w= ould be appropriate to retrieve all 2 million orders in the order table. Plan re-use in this case is undesi= rable. That said, plan re-use for OLTP is normally a good thing because result sets in OLTP tend to be more uniform.<= o:p>
Plan re-use=
The
column usecounts of master..syscacheobjects will show =
reuse
of executable plans. Executab=
le
plans contain user context and unlike compiled plans, are not sharable. In other words, execution pl=
ans
are re-usable but not reentrant.
Thu=
s,
in high concurrency simultaneous execution scenarios with plan re-use, there
will be multiple execution plans for the same obj_id, with usecounts > 1
indicating plan reuse.
Syscacheobjects
Quick
lesson in syscacheobjects. Syscacheobjects is a virtual system table =
that
contains query plans for stored procedures, triggers, adhoc and prepared SQ=
L,
check constraints and defaults. SQL 2000 Plan re-use is determined by text
matching of SQL strings.
If y=
ou see
multiple compile plans with very similar SQL strings, this can indicate a p=
lan
re-use problem. Plan re-use for executable plans is indicated by the
column usecounts > 1 in syscacheobjects.
Auto-parameterization=
In
elementary cases, SQL Server automatically parameterizes adhoc SQL strings,
substituting a parameter for a literal string. The objective of
auto-parameterization is to create a more ‘generic’ re-usable p=
lan.
Say this is my adhoc SQL Statement:
select tit=
le
from titles where title_id =3D 'BU1032'
If y=
ou
look at the query plan generated, it auto-parameterizes by substituting the
parameter marker @1 for the literal 'BU1032'. See red arrow below.
![](3D"KB703_SQLServer2000P&TSurvivalGuide_files/image003.gif")
![](3D"KB703_SQLServer2000P&TSurvivalGuide_files/image005.jpg")
Thus=
, if a
second ADHOC SQL statement is issued:
select tit=
le
from titles where title_id =3D '99999'
The =
text
doesn't match HOWEVER, the parameter marker substitution done by
auto-parameterization allows the SQL strings to be treated as identical, and
thus the first plan is re-used. Furthermore,
when the plan is re-used, the usecounts column in master..syscacheobjects is
incremented by 1. Thus, after=
the
second ADHOC SQL statement, the usecounts column for the executable plan wi=
ll
be 2.
Auto=
-parameterization
of ADHOC SQL only works in simple cases as above. Complex SQL cannot be auto-paramet=
erized
safely by SQL Server, thus the slightest deviation in the text will result =
in a
new plan.
Sp_executesql
Fortunately,
you can explicitly parameterize your ADHOC or PREPARED SQL to allow for plan
re-use by using the stored procedure sp_executeSQL. Sp_=
executesql can be used to execute a Transact-SQL statem=
ent
or batch that can be reused many times, or that has been built dynamically.=
The
Transact-SQL statement or batch can contain embedded parameters. Provided ONLY the parameter values
change, plan reuse can occur. The
following example illustrates:
drop table #x
declare @var1 int, @var2 int
create table #x (a int, b int, c int)
set nocount on
---- flush query plans in syscacheobjects
dbcc freeproccache
---- sp_executeSQL can be u=
sed to manually
parameterize adhoc and prepared SQL in
---- order to create re-usa=
ble
execution plans for INSERT statements
select @var1 =3D 1, @var2 =3D 10
exec sp_executesql N'insert #x values (@a,@b,1)',N'@a = int,@b int',@a=3D@var1,
@b=3D@var2
select @var1 =3D 2, @var2 =3D 20
exec sp_executesql N'insert #x values (@a,@b,1)',N'@a = int,@b int',@a=3D@var1,
@b=3D@var2
select @var1 =3D 3, @var2 =3D 30
exec sp_executesql N'insert #x values (@a,@b,1)',N'@a = int,@b int',@a=3D@var1,
@b=3D@var2
---- no plan re-use due to
different text in SQL string.
Notice values clause dif=
fers
select @var1 =3D 99, @var2 =3D 100
exec sp_executesql N'insert #x values (1,@a,@b)',N'@a int,@b int',
@a=3D@var1,@b=3D@var2
exec sp_executesql N'insert #x values (1,@b,@a)',N'@a int,@b int', &= nbsp; @a=3D@var1,@b=3D@var2
-- @var1=3D1, 2, 3 reuse=
same
plan. Insert statements ident=
ical,
parms differ. Plan can be re-=
used.
-- @var1=3D99 have diffe=
rent
text in SQL statement. Text mismatch in values
clause means insert statements differ even though parms are identical. NO plan re-use.
---- sp_executeSQL can be u=
sed to manually
parameterize adhoc and prepared SQL in
---- order to create re-usa= ble execution plans for SELECT statements
DECLARE @joblevel INT, @jobid INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
/* Build the SQL string onc=
e. */
SET @SQLString =3D
N= 'SELECT * FROM pubs.dbo.employee WHERE job_lvl =3D @jlvl and job_id >=3D @jid'
---- Specify the parameter =
format
once.
SET @ParmDefinition =3D N'@jlvl tinyint,@jid tinyint' =
---- Execute the string wit=
h the
first parameter values.
SELECT @joblevel =3D 35,@jobid =3D 10
EXECUTE sp_executesql @SQLString, @ParmDefinition,
= &nb= sp; @jlvl=3D@joblevel,@jid=3D@jobid
----- Execute again with th=
e new
parameter values. Same plan is
re-used
SELECT @joblevel =3D 75, @jobid =3D 9
EXECUTE sp_executesql @SQLString, @ParmDefinition,
= &nb= sp; @jlvl=3D@joblevel,@jid=3D@jobid
---- look at execution plan
usecounts. usecounts > 1
indicates plan re-use
select bucketid,cacheobjtype,objtype,dbid,objid, useco= unts, sql
from master..syscacheobjects
order by sql
Syscacheobjects (re-use=3Duscounts>1)
|
=
bucketid &nbs=
p;
|
= Cacheobjtype<= o:p> |
=
dbid &nbs=
p; &=
nbsp; &nbs=
p; &=
nbsp; &nbs=
p; &=
nbsp; &nbs=
p; &=
nbsp; &nbs=
p; &=
nbsp; &nbs=
p; &=
nbsp;
&nbs=
p; &=
nbsp; &nbs=
p; &=
nbsp; &nbs=
p; &=
nbsp; &nbs=
p; &=
nbsp; |
||||
|
5366 |
=
Executable Plan |
=
Prepared |
=
5 |
325048227 |
3 |
(@a int,@b int)in=
sert
#x values (@a,@b,1) |
|
5366 |
325048227 |
=
1=
|
(@a
int,@b int)insert #x values (@a,@b,1) |
|||
|
5855 |
714236566 |
(@a
int,@b int)insert #x values (1,@a,@b) |
||||
|
5855 |
714236566 |
=
1=
|
(@a
int,@b int)insert #x values (1,@a,@b) |
|||
|
3734 |
355468203 |
1 |
(@a
int,@b int)insert #x values (1,@b,@a) |
|||
|
3734 |
355468203 |
=
1=
|
(@a
int,@b int)insert #x values (1,@b,@a) |
|||
|
3100 |
Executable Plan |
Prepared |
5 |
786273865 |
2 |
(@jlvl tinyint,@jid tinyint)SELECT * FROM pubs.dbo.employee WHERE
job_lvl =3D @jlvl and job_id >=3D @jid |
|
3100 |
Compiled
Plan |
Prepared |
5 |
786273865 |
1 |
(@jlvl tinyint,@jid tinyint)SELECT * FROM
pubs.dbo.employee WHERE job_lvl =3D @jlvl and job_id >=3D @jid |
|
=
32 |
Extended Proc |
Proc |
741577680 |
7 |
=
sp_ |
|
|
671 |
Parse Tree |
Check |
=
1=
|
437576597 |
=
1=
|
CK_emp_id |
Analyzing Plan re-use
Plan re-use is an important aspect of high performance OLTP
systems. By definition, stored
procedures are parameterized and are subject to plan re-use. Adhoc and Prepared SQL can fail th=
e test
of text matching, thus plans will NOT be re-used. The stored procedures
sp_analyze_compiled_plans and sp_analyze_executable_plans can assist in
identifying plan re-use issues requiring parameterization. The idea is to identify similar SQL
statements (e.g. same select li=
st, from clause, and the only differen=
ce is
frequently the where clause.
Such cases are perfect examples of where sp_executesql can
parameterize SQL strings allowing for plan reuse.
Sp_analyze_compiled_plans
if
exists (select 1 from sysobjects where name =3D 'sp_analyze_compiled_plans'=
)
&=
nbsp; drop
proc sp_analyze_compiled_plans
go
create
procedure sp_analyze_compiled_plans (@SQL_length int=3D80)
as
---------------------------------------------------------------=
--------------------------------
--
This stored procedure is provided "AS IS" with no warranties, and
confers no rights.
--
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
--
--
Analyze plan re-use - find similar SQL plans
--
--
SQL Server uses text matching of queries in
--
Syscacheobjects to determine if a plan can be re-used.
--
If not, a new plan is optimized.
--
If you have plan counts very high for a SQL string,
--
this is a possible plan re-use issue.
--
--
There is a single compile plan, 1 to N execution plans
-- (each concurrent execu=
tion
has own execution plan.)
--
---------------------------------------------------
select
'Count compile plans matching ' + convert(varchar(10),@SQL_Length)
+ ' byte SQL string in syscacheobjects. h=
igh
plan counts=3Dpossible plan re-use issue.’
+ ‘ Check parameterization with
sp_executeSQL or using stored procs'
Select
'#plans'=3Dcount(*), 'SQLstring'=3Ddbo.sql_signature(sql,@SQL_Length)
from
master..syscacheobjects
where
cacheobjtype =3D 'Compiled Plan'
Group
by dbo.sql_signature(sql,@SQL_Length)
Order
by #plans desc
go
exec
sp_analyze_compiled_plans
Sp_analyze_executable_plans
sp_analyze_executable_plans looks for executable plan counts and
average usecounts, both important measures of plan re-use.
if
exists (select 1 from sysobjects where name =3D 'sp_analyze_executable_plan=
s')
&=
nbsp; drop
proc sp_analyze_executable_plans
go
create
procedure sp_analyze_executable_plans (@SQL_length int=3D80)
as
---------------------------------------------------------------=
--------------------------------
--
This stored procedure is provided "AS IS" with no warranties, and
confers no rights.
--
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
--
--
Analyze plan re-use - look at counts and averge usecounts for SQL executable
plans
--
--
SQL Server uses text matching of queries in
--
Syscacheobjects to determine if a plan can be re-used.
--
If not, a new plan is optimized.
--
If you have plan counts very high for a SQL string,
--
this is a possible plan re-use issue.
--
--
There is a single compile plan, 1 to N execution plans
-- (each concurrent execu=
tion
has own execution plan.)
--
---------------------------------------------------
select
'Count executable plans matching ' + convert(varchar(10),@SQL_Length)
+ ' byte SQL string in syscacheobjects. h=
igh
plan counts=3Dpossible plan re-use issue.’
+ ‘ Check parameterization with
sp_executeSQL or using stored procs'
Select
--'#plans'=3Dcount(*),
objtype,'#plans'=3Dcount(*),'avguse'=3Davg(usecounts),'SQLstrin=
g'=3Ddbo.SQL_signature(sql,@SQL_Length)
from
master..syscacheobjects
where
cacheobjtype =3D 'Executable Plan'
Group
by objtype, dbo.sql_signature(sql,@SQL_Length)
Order
by objtype, dbo.sql_signature(sql,@SQL_Length) --#plans
desc
go
exec
sp_analyze_executable_plans
Sample output from sp_analyze_executable_plans=
Count executable plans matching 80 byte SQL string in syscacheobjects.
If you have a high executable plan count with low aver= age usecounts (avguse), this means you probably have a plan re-use issue (See r= ed arrow below). Adhoc and prepa= red SQL can be re-written with sp_executeSQL or as stored procedures to achieve plan re-use.
objtype = #plans avguse &n= bsp; SQLstring = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp;
-------- = --------- -------- &n= bsp; ---------------------------------------= ---------------------------------
![](3D"KB703_SQLServer2000P&TSurvivalGuide_files/image006.gif")
Adhoc
10 =
1 select top # #,
ProductID,UnitPrice,Quantity,Discount from
[Orde= r Detail Sim = &nb= sp; = &nb= sp; = = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; =
Prepared &= nbsp; 1 = 1 (@PSTATMAN varbinary(#))SELECT statman
[Orde= rDate],[OrderID],@PSTATMAN) = &nb= sp; = = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; =
Proc
=
1 =
1 get_memobjlist
Proc = ; = 1 = 236 GetCustContact = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; =
Proc = ; = 1 = 426 GetCustInfo = = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp;
Proc = ; = 16 11 GetCustOrderDetails = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; =
Proc = ; = 11 20 &nb= sp; GetCustOrderHistory = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp;
Proc = ; = 13 14 &nb= sp; GetCustProductOrderHistory = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = = &nb= sp; = &nb= sp; =
Proc = ; = 5 = 30 GetEmployeeInfo &n= bsp; = ; &n= bsp; = ; &n= bsp; = ; &n= bsp; = ; &n= bsp; = ; &n= bsp; = ; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp;
Proc = ; = 3 = 139 GetProductInfo = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp;
Proc = ; = 4 = 656 GetRandomCustID = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; =
Proc = ; = 5 = 195 GetRandomEmpID = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; =
Proc = ; = 6 = 73 &nb= sp; GetRandomOrderID = = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp;
Proc = ; = 1 = 418 = GetRandomProductID = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; =
Proc = ; = 1 = 465 GetRandomSupplierID = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; =
Proc = ; = 7 = 66 GetSupplierInfo = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; =
Proc = ; = 15 55 NewCustOrder = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp;
Proc = ; = 11 19 SalesByYear = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp;
Proc = ; = 1 = 1 = sp_analyze_compiled_plans = &nb= sp; = &nb= sp; = = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; =
Proc = ; = 2 = 178 SQL_Signature = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp;
Proc = ; = 3 = 70 UpdCustOrderRequiredDate = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp;
Proc = ; = 3 = 77 UpdCu= stOrderShippedDate = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp; = &nb= sp;
sql_signature
Sql_signature is used to st=
rip sql
strings in syscacheobjects for comparative purposes.
CREATE
FUNCTION SQL_Signature
&=
nbsp; (@p1
ntext, @ParseLength int =3D 4000)
RETURNS
nvarchar(4000)
--
--
This function is provided "AS IS" with no warranties, and confers=
no
rights.
--
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
--
--
Strips query strings in sysprocesses
AS
BEGIN
&=
nbsp; DECLARE
@pos as INT
&=
nbsp; DECLARE
@mode as CHAR(10)
&=
nbsp; DECLARE
@maxlength as INT
&=
nbsp; DECLARE
@p2 as NCHAR(4000)
&=
nbsp; DECLARE
@currchar as CHAR(1), @nextchar as CHAR(1)
&=
nbsp; DECLARE
@p2len as INT
&=
nbsp; SET
@maxlength =3D len(rtrim(substring(@p1,1,4000)));
&=
nbsp; SET
@maxlength =3D case when @maxlength > @parselength
&=
nbsp; &nbs=
p; &=
nbsp; then @parselength else @maxlength end
&nb=
sp; SET
@pos =3D 1;
&nb=
sp; SET
@p2 =3D '';
&nb=
sp; SET
@p2len =3D 0;
&nb=
sp; SET
@currchar =3D ''
&nb=
sp; set
@nextchar =3D ''
&nb=
sp; SET @mode =3D
'command';
&=
nbsp; WHILE
(@pos <=3D @maxlength) BEGIN
&=
nbsp; &nbs=
p; SET @currchar =3D substring(@p1,@pos,1)
&nb=
sp; =
SET
@nextchar =3D substring(@p1,@pos+1,1)
&nb=
sp; =
IF @mode =3D 'c=
ommand'
BEGIN
&=
nbsp; &nbs=
p; &=
nbsp; SET
@p2 =3D left(@p2,@p2len) + @currchar
&=
nbsp; &nbs=
p; &=
nbsp; SET
@p2len =3D @p2len + 1
&=
nbsp; &nbs=
p; &=
nbsp; IF
@currchar in (',','(',' ','=3D','<','>','!') and
&=
nbsp; &nbs=
p; &=
nbsp; @nextchar between '0' and '9'
BEGIN
&=
nbsp; &nbs=
p; &=
nbsp; &nbs=
p; set @mode =3D 'number'
&nb=
sp; =
&nb=
sp; =
SET
@p2 =3D left(@p2,@p2len) + '#'
&nb=
sp; =
&nb=
sp; =
SET
@p2len =3D @p2len + 1
&nb=
sp; =
&nb=
sp; =
END
&nb=
sp; =
&nb=
sp; IF @currchar =
=3D ''''
BEGIN
&=
nbsp; &nbs=
p; &=
nbsp; &nbs=
p; set
@mode =3D 'literal'
&=
nbsp; &nbs=
p; &=
nbsp; &nbs=
p; SET @p2 =3D left(@p2,@p2len) + '#'''
&nb=
sp; =
&nb=
sp; =
SET @p2len =3D =
@p2len +
2
&=
nbsp; &nbs=
p; &=
nbsp; &nbs=
p; END
&=
nbsp; &nbs=
p; &=
nbsp; END
&=
nbsp; &nbs=
p; ELSE
IF @mode =3D 'number' and @nextchar in (',',')',' ','=3D','<','>','!'=
)
&=
nbsp; &nbs=
p; &=
nbsp; SET
@mode=3D 'command'
&=
nbsp; &nbs=
p; ELSE
IF @mode =3D 'literal' and @currchar =3D ''''
&=
nbsp; &nbs=
p; &=
nbsp; SET
@mode=3D 'command'
&=
nbsp; &nbs=
p; SET
@pos =3D @pos + 1
&=
nbsp; END
&=
nbsp; RETURN
@p2
END
GO
Recompilation
For background on SQL Server 2000 recompilation, refer to
“SQL Server 2000 Recompilation” at http://msdn.microsoft.com.
SQL Profiler 2000 can be used to track the instances of stored
procedure recompilation. SQL =
Server
SP2 provides additional detail on the reasons for recompilation using the
EventSubClass data column as follows:
Tracking recompilation in a Profiler tr= ace
1)
On the Profiler menu, click on ‘new trace’ and conn=
ect
to the appropriate SQL Server.
2)
The Trace Properties
dialog will appear. Go to Events tab, and then expand the S=
tored
Procedure event class.
a)
Select SP:recompile, SP:Starting, SP:Completed, SP:StmtStarting,
and SP:StmtCompleted (to see the stored procedure statement causing the
recompilation)
3)
Go to the Data Columns=
tab, add EventSubClass.
EventSubClass provides a numeric value that describes the reason for=
the
recompile. The reasons for re=
compilation
reported by EventSubClass data
column (description and values) are:
EventSubClass
|
EventSubClass |
Des=
cription |
|
1 |
Schema,
bindings or permissions changed between compile or execute. |
|
2 |
Statistics
changed. |
|
3 |
Recompile DNR (Deferred Name Resolution). Object did not exist at compile
time. Recheck at run time.<=
/span> |
|
4 |
Set option changed in batch.<= o:p> |
|
5 |
Temp
table schema, binding or permission changed. |
|
6 |
Rmt Rowset schema, bindings or permissions changed (Remote e.=
g.
Linked Server schema, statistics etc.) |
Recompilations will not
appear in wait types although; there are serialization, locking, and
concurrency issues when plans are recompiled.
Set options are maintain=
ed
in the setopt column of master..syscachedobjects. If set options are changed during =
execution,
a recompile may occur. Not al=
l set
options cause recompilation. =
See
“SQL Server 2000 Recompilation” white paper for details.
Writing recompilation info in the errorlog=
Recompilation messages can be written to SQL Server’s
Errorlog using trace flags tracking recompilation.
Trace Flags
Dbcc traceon(205,8721) will report recompilation, autostats, and schema changes when they occur in the errorlog. The detail provided is not as comprehensive as Profiler which provides the statement and reasons for reco= mpilation.
Sample Errorlog recompilation entries
|
Recompile |
2002-03-20 17:14:45.72 spid54 Recompile issued :
ProcName: test_recompile =
; LineNo:12 =
; StmtNo:
12 |
|
Schema Change |
2002-03-20 17:45:14.94 spid55 Schema Change: Tbl D=
bid: 2
Objid: 2025058250 RowModCnt: 9 RowModLimit: 6 |
|
Autostats |
2002-03-20 17:47:41.86 spid56 AUTOSTATS: CREATED D=
bid =3D
4 Tbl: RTblNamedObj(Z_BranchID_Z, Z_VS_Z, Z_VE_Z) Rows: 2196 CrtCnt: 3 Du=
r:
280ms |
Statistical (rows changed) thresholds
The following thresholds govern the recompilation of permanent and temporary tables – recorded as EventSubClass =3D 2 –> Statistics changed.
|
Table Type<= o:p> |
Empty Condi=
tion |
Threshold w=
hen
empty |
Threshold w=
hen not
empty |
|
Permanent |
< 500 Rows |
# of Changes >=3D 500<= o:p> |
# of Changes >=3D 500 =
+ (20% of
Cardinality) |
|
Temporary |
< 6 Rows |
# of Changes >=3D 6 |
# of Changes >=3D 500 =
+ (20% of
Cardinality) |
|
Table Variable |
No thresholds |
No thresholds |
No thresholds |
Avoiding re-comps based on rows changed (EventSubClass=3D2)
EventSubClass =3D 2 can be =
minimized by
using table variables that are not subject to the rows changed thresholds o=
r by
using OPTION(KEEPFIXED PLAN) in selects, updates, and deletes. However it can not be used if exis=
ts
(select ….. OPTION(KEEPFIXED PLAN) Here’s a work around:
-- works:
select
@belongsempty =3D 1 where not exists (select * from Belong)
OPTION (
KEEPFIXED PLAN )
-- does not work with if exists – Syntax
error
&nb=
sp;
if exists ( select * from Belong OPTION ( KEEPFIXED PLAN ) )
The cavea=
t to
table variables is scope. Tab=
le
variables cannot be passed to other stored procedures. In some cases, if the table variab=
les
contain a very large number of rows, the query plans for say, joins of the
table variables, may be suboptimal.
In such cases, it may be beneficial to get a recompile using temp ta=
bles
that are subject to the rows changed threshold.
Stored Procedure Design issues with temporary = tables (EventSubClass=3D5)
Prior to SQL Server 7, a common recommendation was to create and
populate a temporary table #T1 in proc P1 and then access #T1 in proc P2.
In SQL7 and SQL2000, this practice will result in a recompilati=
on
with EventSubClass=3D5. becau=
se the
object id for #T1 changes for each user.&n=
bsp;
Because SQL7 and SQL2000 resolves based on object ID, there will be =
an
EventSubClass=3D5 recompile for each execution of P2.
The P1, P2 design is not necessary with SQL2000 because dynamic
recompilation will occur based on rowsmodified. Rowsmodified can be avoided with
KEEPFIXED PLAN.
For Sybase Migrations:
Sybase introduced trace flag 299 to resolve temp tables based on NAME
rather than ID. Consequently,
schema must match or corruption results.&n=
bsp;
SQL Server does not have Name resolution, thus EventSubClass =3D 5 w=
ill
occur with this application design.
Set Options
Set options causing recompilation (eventsubcla= ss=3D4)
Changing the following set options will trigger a
re-compilation:
- Generally,=
those
that affect query behavior or result sets such as
- ANSI_DEFA=
ULTS
- ANSI_NULL=
_DFLT_OFF
- ANSI_NULL=
_DFLT_ON
- ANSI_NULL=
S
- ANSI_PADD=
ING
- CONCAT_NU=
LL_YIELDS_NULL
- FORCEPLAN=
- ANSI_DEFA=
ULTS
- Other set
options
- ANSI_WARN=
INGS
- ARITHABOR=
T
- LANGUAGE<= o:p>
- NUMERIC_R=
OUNDABORT
- QUOTED_ID=
ENTIFIER
Cache management
Query plans, sorts, user connections, locks, utility pages, PSS structures and so forth live in real= SQL Server memory. In 32-bit, real memory is up to 3GB with the /3GB switch, otherwise 2GB is the maximum.
Perfmon counters for SQL compilation and re-compilation=
The PERFMON counters for pl=
an
management are as follows:
PERFMON<=
span
style=3D'color:blue'>
1. Batch requests/sec (Perfmon=
)
2. SQL Compilations/sec (Perfm=
on)
3. SQL Recompilations/sec
(Perfmon)
Batch requests measures tot=
al SQL
requests. Since SQL
Compilations/sec includes SQL recompiles/sec, we must subtract SQL
recompilations from SQL Compilations to get initial compilations. Initial compiles provides a measur=
e of
plan re-use.
Plan re-use =3D (Batch requ=
ests
– Initial Compilations) / Batch requests
Initial Compilations =3D SQL
Compilations – SQL Recompilations
Profiler events for plan re-use, compilation &= amp; re-compilation
The Profiler events that tr=
ack
cache management include:
PROFILER=
4. SP:CacheMiss (event ID 34 in
Profiler)
5. SP:CacheInsert (event ID 35=
in
Profiler)
6. SP:CacheRemove (event ID 36=
in
Profiler)
7. SP:Recompile (event ID 37 in
Profiler)
8. SP:CacheHit (event ID 38 in
Profiler)
For more details see sp_trace_setevent in SQL Server Books OnLi=
ne.
Counting the events will give you a picture of what is happenin=
g in
cache.
Select event_id, count(*)
From trace_table
Group by event_id
Order by event_id
CacheRemove indicates SQL is discarding a query plan (e.g. least
recently used) to alleviate memory pressure.
Memory limits for 32-bit SQL 2000
In SQL Server 2000 32-bi=
t,
the upper limit of memory for query plans is 2 GB unless the /3gb switch is
used. It is important to note=
that
the 2GB limit applies to query plans, sorts, user connections, locks, utili=
ty
pages, PSS structures and so forth.
Only data and index pages can go into AWE memory.
Under memory pressure, SQL discards objects and/or pages to make
room for the workload. =
The
oldest unreferenced (e.g. least recently used) objects and pages are discar=
ded
first. Data and index pages c=
an
also be discarded; however query plans can only reside in real memory. See Memory limits above for 32-bit=
SQL Server
2000..
The important aspect is once a query plan is removed from cache=
, it
must be compiled again if and when the same proc or adhoc SQL is called aga=
in.
SQL 2000 64-bit does not
have these limitations on the size of proc cache.
Determining what is in real memory cache
Certain objects such as
query plans, sorts, user connections, locks, utility pages, PSS structures
optimization pages and so forth can only reside in 2GB of real SQL Server
memory. If the /3GB switch is=
used,
the limit increases to 3GB. D=
ata
and index pages can also live in real memory if there is room to
accommodate.
Only data and index pages
can go into AWE memory.
Get_memobjlist
Get_memobjlist breaks down =
real
memory listing objects such as stored procedure, adhoc, and prepared compile
and executable plans, user connections, locks, utility pages, PSS structure=
s,
sorts, optimization pages and so forth.
At the end of the report,
syscacheobjects is summarized by cacheobjtype and objtype.
if
exists (select 1 from sysobjects where name =3D 'get_memobjlist')
&=
nbsp; drop
proc get_memobjlist
go
create
proc get_memobjlist
as
--
--
This stored procedure is provided "AS IS" with no warranties, and
confers no rights.
--
Use of included script samples are subject to the terms specified at http:/=
/www.microsoft.com/info/cpyright.htm
--
--
1) lists memory objects
--
2) provides a breakdown of syscacheobjects
--
Note: to print out the contents of 2-3gb real memory,
--
you must have the /T 3654 trace flag on at SQL STARTUP (dbcc traceon will n=
ot
work)
--
set
nocount on
create
table #memobjlist
(ID
int, pages int, bytes int, MemoryObject varchar(80))
insert
into #memobjlist
exec
('dbcc memobjlist')
if
@@rowcount =3D 0
&=
nbsp; begin
&=
nbsp; &nbs=
p; print
'To get contents of memobjlist, add trace flag /T3654 as a SQL startup para=
meter'
&=
nbsp; &nbs=
p; return
-999
&=
nbsp; end
--
show object list, order by memoryObject
select * from #memobjlist
order
by MemoryObject
--
get totals from memobjlist
select
totPages=3Dsum(pages), totBytes=3Dsum(bytes)
from
#memobjlist
--
get overall counts from syscacheobjects by cacheobjtype, objtype
select
CASE WHEN
(GROUPING(cacheobjtype)=3D1) THEN '**Total'
&=
nbsp; &nbs=
p; ELSE
ISNULL(cacheobjtype,'UNKNOWN') END as cacheobjtype,
&=
nbsp; CASE
WHEN (GROUPING(objtype)=3D1) THEN '*Total'
&=
nbsp; &nbs=
p; ELSE
ISNULL(objtype,'UNKNOWN') END as objtype,
&=
nbsp; sum(pagesused)
as PagesUsed,count(*) as ObjCount
from
master..syscacheobjects nolock
group
by cacheobjtype, objtype with cube
go
exec
get_memobjlist
Sample output (partial)
Note that there is a single=
copy of
compiled plans for each stored procedure (e.g. Compile_GetCustOrderDetails)
while there may be multiple copies of executable plans
(Execute_GetCustOrderDetails). The
multiple executable plans indicate concurrent executions of the stored
procedure.
ID = = pages bytes = MemoryObject = = &nb= sp; = &nb= sp;
----------- &nbs= p; -------- ----------- ----------------= ---
16303 1 = 1124 Compile_Adhoc (spid=3D51)
16285 1 = 1168 Compile_Adhoc (spid=3D51)
1072 2 5708 Compile_Adhoc (spid=3D51)
16291 14
760 = 2 = 5282 Compile_GetCustContact
243 = 2 = 6637 Compile_GetCustInfo
288 = 4 = 13485 = Compile_GetCustOrderDetails
247 = 4 = 14682 = Compile_GetCustOrderHistory
245 = 4 = 14527 = Compile_GetCustProductOrderHis
264 = 2 = 5627 Compile_GetEmployeeInfo
397 = 3 = 8029 Compile_GetProductInfo
307 = 4 = 11719 = Compile_GetRandomCustID
321 &= nbsp; 4 = 13045 Compile_GetRandomEmpID
360 = 2 = 5514 Compile_GetRandomOrderID
505 = 2 = 5546 Compile_GetRandomProductID
315 = 2 = 5534 Compile_GetRandomSupplierID
259 = 2 = 6238 Compile_GetSupplierInfo
249 = 13 = 48328 Compile_NewCustOrder
1450 12 = 47788 Compile_SalesByCategoryByYear
433 = 4 = 14860 = Compile_SalesByYear
6576 3 = 11757 = Compile_sp_analyze_compiled_pl
16237 4 = 14208 = Compile_sp_analyze_executable_
6961 = 6 = 23498 = Compile_SQL_Signature
274 = 3 = 8532 Compile_UpdCustOrderRequiredDa
269 = 3 = 9644 Compile_UpdCustOrderShippedDat
3 = = 11 74584 = CSqlManager Global allocator
16305 2 = 0 = DbccOperation
18 = 2 = 256 = Deadlock Monitor
12 = 1 = 0 = Exchange Ports
13 = = 1 = 0 = Exchange Xid
88 = = 2 = 8254 &= nbsp; Execute_Adhoc (spid=3D51)
73 = = 1 1256 Execute_Adhoc (spid=3D51)
16304 1 = 436 Execute_Adhoc (spid=3D51)
16286 1 = 476 Execute_Adhoc (spid=3D51)
1096 1 = 3388 &= nbsp; Execute_Adhoc (spid=3D51)
16289 1 = 3874 Execute_get_memobjlist
764 = 1 = 3302 &= nbsp; Execute_GetCustContact
305 = 1 = 4344 Execute_GetCustInfo
553 = 3 = 12570 = Execute_GetCustOrderDetails
966
=
3 =
12570 Execute_GetCustOrderDetails
501
=
3 =
12570 =
Execute_GetCustOrderDetails 500
=
3 =
12570 =
Execute_GetCustOrderDetails 9038 =
3 =
12570 =
Execute_GetCustOrderDetails 9019 3 =
12570 Execute_Get=
CustOrderDetails 8997 3 =
12570 =
Execute_GetCustOrderDetails 8978 3 =
12570 =
Execute_GetCustOrderDetails 8954 3 =
12570 =
Execute_GetCustOrderDetails 8948 3 =
12570 =
Execute_GetCustOrderDetails 8891 3 =
12570 =
Execute_GetCustOrderDetails 8878 3 =
12570 =
Execute_GetCustOrderDetails 8759 3 =
12570 =
Execute_GetCustOrderDetails 8659 3 =
12570
Execute_GetCustOrderDetails 8634 3 =
12570 =
Execute_GetCustOrderDetails 1709 3 =
12570 =
Execute_GetCustOrderDetails 1088 3 =
11398 =
Execute_GetCustOrderHistory 1061 =
3 11398
Execute_GetCustOrderHistory 998
=
3 =
11398 =
Execute_GetCustOrderHistory 5924 3 =
11398 =
Execute_GetCustOrderHistory 5917 3 =
11398 =
Execute_GetCustOrderHistory 627
=
3 =
11398
Execute_GetCustOrderHistory 626
=
3 =
11398 =
Execute_GetCustOrderHistory 625
=
3 =
11398 =
Execute_GetCustOrderHistory 624
=
3 =
11398 =
Execute_GetCustOrderHistory 619
=
3 =
11398 =
Execute_GetCustOrderHistory 618
=
3 =
11398 =
Execute_GetCustOrderHistory 521
=
3 =
13026 =
Execute_GetCustProductOrderHis 494
=
3 =
13026 =
Execute_GetCustProductOrderHis 493
=
3 =
13026 =
Execute_GetCustProductOrderHis 491
=
3 =
13026 =
Execute_GetCustProductOrderHis 805
=
3 =
13026 =
Execute_GetCustProductOrderHis 987
=
3 =
13026 =
Execute_GetCustProductOrderHis 979
=
3 =
13026 =
Execute_GetCustProductOrderHis 1138 3 =
13026 =
Execute_GetCustProductOrderHis 8400 3 =
13026 =
Execute_GetCustProductOrderHis 8386 3 =
13026 =
Execute_GetCustProductOrderHis 9167 3 =
13026 =
Execute_GetCustProductOrderHis 16143 3 =
13026 =
Execute_GetCustProductOrderHis 13387 3 =
13026 =
Execute_GetCustProductOrderHis 409
=
1
3549 Execute_GetEmployeeInfo 385
=
1 =
3549 Execute_GetEmployeeInfo 328
=
1 =
3549 Execute_GetEmployeeInfo 326
=
1 =
3549 Execute_GetEmployeeInfo 319
=
1 =
3549 Execute_GetEmployeeInfo 525
=
2 =
5831 Execute_GetProductInfo 517
=
2 =
5831 Execute_GetProductInfo 503
=
2 =
5831 Execute_GetProductInfo 365
=
2 =
5138 Execute_GetRandomCustID 375
=
2 =
5138 Execute_GetRandomCustID 368
=
2 =
5138 Execute_GetRandomCustID 311
=
2 =
5138 Execute_GetRandomCustID 325
=
2 =
4093 Execute_GetRandomEmpID 327
=
2 =
4093 Execute_GetRandomEmpID 386
=
2 =
4093 Execute_GetRandomEmpID 329
=
2 =
4093 Execute_GetRandomEmpID 410
=
2 =
4093 Execute_GetRandomEmpID 377
=
1 =
2926 Execute_GetRandomOrderID 392
=
1 =
2926 Execute_GetRandomOrderID 474
=
1 =
2926 Execute_GetRandomOrderID 367
=
1 =
2926 Execute_GetRandomOrderID 374 =
1 =
2926 Execute_GetRandomOrderID 364
=
1 =
2926 Execute_GetRandomOrderID 534
=
1 =
3069 Execute_GetRandomProductID 482
=
1 =
3071 Execute_GetRandomSupplierID 353 =
=
1 =
4121 Execute_GetSupplierInfo 346
=
1 =
4121 Execute_GetSupplierInfo 336
=
1 =
4121 Execute_GetSupplierInfo 469
=
1 =
4121 Execute_GetSupplierInfo 438
=
1 =
4121 Execute_GetSupplierInfo 317
=
1 =
4121 Execute_GetSupplierInfo 313
=
1 =
4121 Execute_GetSupplierInfo 545
=
3 =
14341 =
Execute_NewCustOrder
541
=
3
14341 Execute_NewCustOrder 605
=
3 =
14341 =
Execute_NewCustOrder
733
=
3 =
14341 =
Execute_NewCustOrder
623
=
3 =
14341 =
Execute_NewCustOrder
576
=
3 =
14341 =
Execute_NewCustOrder
575
=
3 =
14341 =
Execute_NewCustOrder
574
=
3 =
14341 =
Execute_NewCustOrder
573
=
3 =
14341 =
Execute_NewCustOrder
572
=
3 =
14341 =
Execute_NewCustOrder
571 =
=
3 =
14341 =
Execute_NewCustOrder
570
=
3 =
14341 =
Execute_NewCustOrder
569
=
3 =
14341 =
Execute_NewCustOrder
568
=
3 =
14341 =
Execute_NewCustOrder
567
=
3 =
14341 Execute_NewCustOrder 646
=
2 =
8894 Execute_SalesByYear 768
=
2 =
8894 Execute_SalesByYear 597
=
2 =
8894 Execute_SalesByYear 589
=
2 =
8894 Execute_SalesByYear 612
=
2 =
8894 Execute_SalesByYear 496
=
2 =
8894 Execute_SalesByYear 8918 2 =
8894 Execute_SalesByYear 2438 2 =
8894 Execute_SalesByYear 2418 2 =
8894 Execute_SalesByYear 6551 2 =
8894 Execute_SalesByYear 6994 2 =
8894 Execute_SalesByYear 6582 1 =
1582 Execute_sp_analyze_compiled_pl 16242 1 =
1592 Execute_sp_analyze_executable_ 6990 24
138834 Execute_SQL_Signature 7031 24
138878 Execute_SQL_Signature 473
=
1 =
3500 Execute_UpdCustOrderRequiredDa 358
=
1 =
3500 Execute_UpdCustOrderRequiredDa 366
=
1 =
3500 Execute_UpdCustOrderRequiredDa 373
=
1 =
4170 Execute_UpdCustOrderShippedDat 391
=
1 =
4170 Execute_UpdCustOrderShippedDat 376 1 =
4170 Execute_UpdCustOrderShippedDat 10 =
=
50 160064 Lock Block=
s 11 =
=
31
80544 =
Lock Owners 5 =
=
2 =
0 =
Lock=
Bytes 476
=
1 =
1372 Parse_CK_Discount 477
=
1 =
1076 Parse_CK_Quantity 478
=
1 =
1086 Parse_CK_UnitPrice 434
=
1 =
4138 Parse_Order Subtotals 14 =
=
1 =
2493 &=
nbsp;
PSS (spid=3D1) 60 =
=
4 =
16027 =
PSS (spid=3D10) 62 =
=
4 =
17485 =
PSS (spid=3D11) 63 =
=
4 =
17485 =
PSS (spid=3D12) 15 =
=
1 =
2493 PSS (spid=3D2) 16 =
=
1 =
4075 PSS (spid=3D3) 17 =
=
1 =
2493 PSS (spid=3D4) 57 =
=
4 =
17485 =
PSS (spid=3D5) 24 =
=
4 =
16027 =
PSS (spid=3D6) 25 =
=
1 =
3951 PSS (spid=3D7) 58 =
=
4 =
17485 =
PSS (spid=3D8) 59 =
=
4 =
16027 PSS (spid=
=3D9) 6 =
=
7 =
32808 =
Remote Session Cache 9 =
=
2 =
116
Replication 1 =
=
283 2186812
7 = = 3 = 8260 Resource sort cache
2 &= nbsp; 12 = 3172 Resource Xact Memory Allocator
4 = = 57 = 302548 Server Global
8 = = 3 = 6572 SQLTrace Global allocator
64 = = 5 = 28437 = SRVPROC
7016 3 = 13316 SRVPROC
5053 3 = 19665 = SRVPROC
4380 3 = 19665 = SRVPROC
Syscacheobjects is summariz=
ed by
cacheobjtype and objtype.
Note the size of adhoc and prepared objtypes. These are the object types that
frequently need to be manually parameterized with sp_executeSQL or re-writt=
en
as stored procedures. See
sp_analyze_executable_plans to determine if parameterization is needed.
cacheobjtype = objtype &n= bsp; PagesUsed ObjCount
----------------- &nbs= p; = -------- &= nbsp; ----------- &= nbsp; -----------
Compiled Plan Adhoc= 30 = = 17
Compiled Plan Prepa= red = 2 = = 1
Compiled Plan Proc<= span style=3D'mso-spacerun:yes'> &= nbsp; 99 = = 22
Compiled Plan *Tota= l 131= = 40
Executable Plan = = Adhoc 14 = = 13
Executable Plan = = Prepared &= nbsp; 1 = = 1
Executable Plan = = Proc &= nbsp; 289 = 110
Executable Plan = = *Total 304= = 124
Parse Tree &= nbsp; Check 3 = = 3
Parse Tree &= nbsp; View &= nbsp; 1 = = 1
Parse Tree &=
nbsp; *Total 4
**Total = = *Total 439= = 168
**Total = = Adhoc 44 = = 30
**Total = = Check 3 = = 3
**Total = = Prepared &= nbsp; 3 = = 2
**Total = = Proc = 388 = 132
**Total = = View &= nbsp; 1 = = 1
Waits and Queues
For a description of the Waits & Queues performance methodo=
logy,
refer to the document SQL Server 2000 Performance using Waits & Queues
document.
<= o:p>
W=
ait
Types
SQL Server user connections execute queries which in turn, ask =
for
resources. Resources can incl=
ude
CPU, memory, locks, IO, and network bandwidth. If a thread requests resources and=
has
to wait for the resource, a wait state is set to capture the performance.
The wait state is contained in master..sysprocesses in the colu=
mns
waittype, and lastwaittype.
Lastwaittype is a character description of the last wait state for t=
his
thread. It is not reset until
another wait state occurs. Wa=
ittype
is a varbinary wait state that is the current wait state.
There are up to 60 different wait types. For a description of wait types se=
e SQL
Server 2000 Performance using Waits & Queues.doc.
Sysprocesses
When a thread waits, the columns waittype (binary(2)), waitt=
ime
(int) and lastwaittype (nchar(32)) and waitresource.. The values for
waittype and lastwaittype columns are contained in memory structures in SQL
Server.
Last=
waittype is =
a character
description of the last wait type for this thread. It is not reset until another wait=
state
occurs. Thus, a non-blank lastwaittype means the thread had=
at
least one wait state.
The current wait status is recorded in waittype. If waittime is 0, this means the thr=
ead is
either currently running or not waiting.&n=
bsp;
DBCC SQLPERF(waitstats)
DBCC SQLPERF(WAITSTATS) reports aggregated wait types that map to the
above wait types in master..sysproc=
esses.
(see WAIT TYPES). The limitation of dbcc sqlperf(wait=
stats)
is it includes wait types that are internally used such as synchronization
objects that the user cannot affect by performance tuning SQL statements.
Track_waitstats
DBCC SQLPERF(WAITSTATS) reports aggregated wait types that map to the
above wait types in master..sysproc=
esses.
(see WAIT TYPES). While all wait types are aggregated=
in
the dbcc sqlperf(waitstats) memory structure, they include internal wait ty=
pes
that
if exists (select 1 from sysobjects where name =3D 'track_waitstats')
<= /span>drop proc track_waitstats
go
CREATE proc track_waitsta= ts (@num_samples int=3D10,@delaynum int=3D1,@delaytype nvarchar(10)=3D'minutes= ')
as
--
-- This stored procedure = is provided "AS IS" with no warranties, and confers no rights.
-- Use of included script= samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
--
-- @num_samples is the nu= mber of times to capture waitstats, default is 10 times
-- default delay interval= is 1 minute
-- delaynum is the delay = interval - can be minutes or seconds
-- delaytype specifies wh= ether the delay interval is minutes or seconds
-- create waitstats table= if it doesn't exist, otherwise truncate
--
set nocount on
if not exists (select 1 f= rom sysobjects where name =3D 'waitstats')
<= /span>create table waitstats ([wait type] varchar(80),
&n= bsp; requests numeric(20,1),
&n= bsp; [wait time] numeric (20,1),
&n= bsp; [signal wait time] numeric(20,1),
&n= bsp; now datetime default getdate())
else truncate table waitstats
dbcc sqlperf (waitstats,clear) -- clear ou= t waitstats
declare @i int,@delay varchar(8),@dt varchar(3), @now datetime, @totalwait numeric(20,1)
<= /span>,@endtime datetime,@begintime datetime
<= /span>,@hr int, @min int, @sec int
select @i =3D 1
select @dt =3D case lower(@delaytype)
<= /span>when 'minutes' then 'm'
<= /span>when 'minute' then 'm'
<= /span>when 'min' then 'm'
<= /span>when 'mm' then 'm'
<= /span>when 'mi' then 'm'
<= /span>when 'm' then 'm'
<= /span>when 'seconds' then 's'
<= /span>when 'second' then 's'
<= /span>when 'sec' then 's'
<= /span>when 'ss' then 's'
<= /span>when 's' then 's'
<= /span>else @delaytype
end
if @dt not in ('s','m')
begin
<= /span>print 'please supply delay type e.g. seconds or minutes'
<= /span>return
end
if @dt =3D 's'
begin
<= /span>select @sec =3D @delaynum % 60
<= /span>select @min =3D cast((@delaynum / 60) as int)
<= /span>select @hr =3D cast((@min / 60) as int)
<= /span>select @min =3D @min % 60
end
if @dt =3D 'm'
begin
<= /span>select @sec =3D 0
<= /span>select @min =3D @delaynum % 60
<= /span>select @hr =3D cast((@delaynum / 60) as int)
end
select @delay=3D right('0= '+ convert(varchar(2),@hr),2) + ':' +
<= /span>+ right('0'+convert(varchar(2),@min),2) + ':' +
<= /span>+ right('0'+convert(varchar(2),@sec),2)
if @hr > 23 or @min &g= t; 59 or @sec > 59
begin
<= /span>select 'hh:mm:ss delay time cannot > 23:59:59'
<= /span>select 'delay interval and type: ' + convert (varchar(10),@delaynum) + ',' + @delaytype + ' converts to ' + @delay
<= /span>return
end
while (@i <=3D @num_sa= mples)
begin
= insert into waitstats ([wait type], requests, [wait time],[signal wa= it time])
<= /span>exec ('dbcc sqlperf(waitstats)')
<= /span>select @i =3D @i + 1
<= /span>waitfor delay @delay
end
select @now=3Dmax(now),@begintime=3Dmin(now),@endtime=3Dmax(now)
from waitstats where [wai= t type] =3D 'Total'
--- subtract waitfor, sle= ep, and resource_queue from Total
select @totalwait =3D sum= ([wait time]) + 1 from waitstats
where [wait type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE','Total', '***total***') and now =3D @no= w
-- insert adjusted totals= , rank by percentage descending
insert into waitstats sel= ect '***total***',0,@totalwait,@totalwait,@now
select 'start, end, duration'=3D'start: ' + convert(varchar(20),@begintime,20) + ' end: ' + convert(varchar(20),@endtime,20) + ' duration (minutes): ' + convert(varcha= r(10), datediff(mi,@begintime,@endtime))
select 'waitstats samples'=3D@num_samples, 'del= ay, type'=3D convert (varchar(10),@delaynum) + ',' + @delaytype,'waitfor delay interval'=3D@delay
select [wait type],[wait time],percentage=3Dcast (100*[wait time]/@totalwait as numeric(20,1))
from waitstats
where [wait type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE','Total')
and now =3D @now
order by percentage desc<= /p>
go
exec track_waitstats 20,15,'seconds'
Open Transactions, Locks<= /span> & Blocking
Sysprocesses
Open transactions can bl=
ock
other transactions if locks are held.
Open transactions are shown in open_tran of master..sysprocesses. Blocked spids are shown in t=
he blocked
column of master..sysprocesses.
Dbcc inputbuffer(spid)
To print o=
ut the SQL
statement of spids involved in blocking, use dbcc inputbuffer(spid). This is limited to 255 characters.=
::fn_get_sql
After SQL =
Server
2000 SP2, you can retrieve longer commands using:
&=
nbsp; Declare
@handle binary(20)
&=
nbsp; select
@handle=3Dsql_handle
from master..sysprocesses nolock
where spid =3D @blockvictim
&=
nbsp; select
text FROM ::fn_get_sql(@handle)
Syslockinfo
Locking information are =
in syslockinfo. You can display it using sp_lock a=
nd
sp_blockinfo.
Sp_blockinfo=
us=
e master
go=
if=
exists
(select 1 from sysobjects where name =3D ‘sp_blockinfo’)
go=
cr=
eate
proc sp_blockinfo @spid smallint =3D null
as=
--
--
This stored procedure is provided "AS IS" with no warranties, and
confers no rights.
--
Use of included script samples are subject to the terms specified
-- at http://www.microsoft.com/info/cpyright.htm
--
--
B.Newman (
--=
Will
return sp_who2, DBCC INPUTBUFFER and modified sp_lock info for the blocking
spid
--=
or info
on provided spid
de=
clare
@exec varchar(3000)
de=
clare
@cnt int
set
transaction isolation level read uncommitted
set
nocount on
--
Provided a spid as a param?
if=
( @spid
IS NULL )
if=
( @spid
IS NULL )
el=
se
be=
gin
en=
d
go=
ex=
ec
dbo.sp_MS_marksystemobject 'sp_blockinfo'
go=
gr=
ant
execute on sp_blockinfo to public
go=
--=
---------------------------------------------------------------------------=
----
if
object_id ( 'sp_blockinfo_all' ) IS NOT NULL
go=
cr=
eate
proc sp_blockinfo_all @blocked_only varchar(5) =3D 'true', @hostname
nvarchar(256) =3D null
as=
--
--
This stored procedure is provided "AS IS" with no warranties, and
confers no rights.
--
Use of included script samples are subject to the terms specified
-- at http://www.microsoft.com/info/cpyright.htm
--
--
B.Newman (
--=
Calls
sp_blockinfo for either all blocked processes or all process
de=
clare
@spid int
de=
clare
@blocked int
set
nocount on
set
@blocked =3D 0
if=
LOWER(
@blocked_only ) =3D 'y' set @blocked_only =3D 'true'
if=
LOWER(
@blocked_only ) =3D 'n' set @blocked_only =3D 'false'
if=
LOWER(
@blocked_only ) not in ('true', 'false')
be=
gin
en=
d
if=
(
@blocked_only =3D 'false' )
set
transaction isolation level read uncommitted
cr=
eate
table #temp_spid ( spid int )
if=
(
@hostname is null )
be=
gin
en=
d
el=
se
be=
gin
pr=
int
@hostname
en=
d
de=
clare
TEMP_CURSOR_sysprocesses CURSOR LOCAL FORWARD_ONLY for select DISTINCT spid=
op=
en
TEMP_CURSOR_sysprocesses
fe=
tch next
from TEMP_CURSOR_sysprocesses INTO @spid
wh=
ile (
@@FETCH_STATUS =3D 0 )
be=
gin
en=
d
cl=
ose
TEMP_CURSOR_sysprocesses
de=
allocate
TEMP_CURSOR_sysprocesses
dr=
op table
#temp_spid
go=
ex=
ec
dbo.sp_MS_marksystemobject 'sp_blockinfo_all'
go=
gr=
ant
execute on sp_blockinfo_all to public
go=
PERFMON<= span style=3D'mso-bookmark:_Toc4324903'>
Performance Counters =
In general terms, performance counters provide a view = of resource utilization. Combine= d and correlated with wait types, they provide a complete picture of application = and system performance.
The following counters are useful for many performance scenarios. Descriptions and correlations to wait types can be found in the document “SQL Server 2= 000 Performance using Waits & Queues.doc”.
- Memory: Page faults/sec
- Memory: pages/sec
- Physical Disk: Avg. Disk Queue Len=
gth
- Physical Disk: Avg. Disk sec/Trans=
fer
- Physical Disk: Avg. Disk sec/Read<= o:p>
- Physical Disk: Avg. Disk sec/Write=
- Physical Disk: Current Disk Queue =
Length
- Processor: %Processor Time
- SQLServer Access Methods: Forwarded
Records/sec
- SQLServer Access Methods: Full Sca=
ns/sec
- SQLServer Access Methods: Index
Searches/sec
- SQLServer Access Methods: Page
Splits/sec
- SQLServer Access Methods: Range
Scans/sec
- SQLServer Access Methods: Table Lo=
ck
Escalations/sec
- SQLServer Buffer Manager: Checkpoi=
nt
pages/sec
- SQLServer Buffer Manager: Lazy
writes/sec
- SQLServer Buffer Manager: Page Life
expectancy
- SQLServer Databases: Log Flush Wai=
t time
- SQLServer Databases: Log Flush Wai=
ts/sec
- SQLServer General Statistics: User
Connections
- SQLServer Latches: Average Latch W=
ait
Time(ms)
- SQLServer Latches: Latch Waits/sec=
- SQLServer Latches: Total Latch Wai=
t Time
(ms)
- SQLServer Locks: Average Wait Time=
(ms)
- SQLServer Locks: Lock requests/sec=
- SQLServer Locks: Lock Wait Time (m=
s)
- SQLServer Locks: Lock Waits/sec
- SQLServer Memory Manager: Memory g=
rants
pending
- SQLServer SQL Statistics: Auto-Par=
ams
attempts/sec
- SQLServer SQL Statistics: Batch
requests/sec
- SQLServer SQL Statistics: Safe
Auto-Params/sec
- SQLServer SQL Statistics: SQL Comp=
ilations/sec
- SQLServer SQL Statistics: SQL
Re-Compilations/sec
- System: Processor Queue Length
Sysperfinfo
This system table contains the SQL counters that are e= xposed to PERFMON.