January 21, 2011 at 9:49 am
ok..you are right..since it is going to be reusing the same. Thanks for correcting. I am developing a script from the views which you have mentioned, will get back. Thanks
January 21, 2011 at 1:29 pm
GilaMonster (1/21/2011)
I still don't understand why you're interested in that. An execution plan takes up the same amount of space whether it's used once or 10000 times.
OK i have my query ready now
select a.*,b.dbid,db_name(b.dbid) as Dbname, object_name(b.objectid) as objectname,b.objectid from (
select p.cacheobjtype,p.refcounts,p.usecounts,p.size_in_bytes,s.sql_handle,p.plan_handle,s.last_execution_time
from sys.dm_exec_cached_plans p
join sys.dm_exec_query_stats s
on p.plan_handle=s.plan_handle
where (p.cacheobjtype='Compiled Plan' or p.cacheobjtype='Executable Plan')
) a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
where db_name(b.dbid)='MyDBName'
My goal is to findout how many different plans are genrated for each procedure. I think sql is using bad plan for some reason.My above query is giving duplicate records for each plan i am not sure why. I am running the above query in context of MyDBName.
January 21, 2011 at 1:42 pm
sqldba_icon (1/21/2011)
My goal is to findout how many different plans are genrated for each procedure.
One and only one. A procedure can only have one plan in cache at a time (baring different SET options)
My above query is giving duplicate records for each plan i am not sure why. I am running the above query in context of MyDBName.
I guess you didn't notice what I said about the DMVs.
sys.dm_exec_query_stats (one row per statement in the batch/procedure)
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
January 21, 2011 at 2:42 pm
GilaMonster (1/21/2011)
sqldba_icon (1/21/2011)
My goal is to findout how many different plans are genrated for each procedure.One and only one. A procedure can only have one plan in cache at a time (baring different SET options)
My above query is giving duplicate records for each plan i am not sure why. I am running the above query in context of MyDBName.
I guess you didn't notice what I said about the DMVs.
sys.dm_exec_query_stats (one row per statement in the batch/procedure)
I have modified the query to:
select distinct object_name(c.objectid) as objectname,a.*
from (
select p.cacheobjtype,p.refcounts,p.usecounts,p.size_in_bytes,p.plan_handle,
s.last_execution_time
from sys.dm_exec_cached_plans p
join sys.dm_exec_query_stats s
on p.plan_handle=s.plan_handle
where (p.cacheobjtype='Compiled Plan' or p.cacheobjtype='Executable Plan')
) a
--CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
cross apply sys.dm_exec_query_plan(a.plan_handle)c
where db_name(c.dbid)='MyDB'
order by a.last_execution_time asc
Gail i am confused here. This is my proc i am executing "
declare @RId nvarchar(max)
exec usp_Ch_proc_1_1 @RId=256593" in db MYDB. Then i run the above proc to analyze cache with the results attached in excel sheet. I see the proc "usp_Ch_proc_1_1 " is ran three times ? No one else is running anything against this db. I am confused how come proc is running three times in cache when only it is executed once. Thanks again for your suggestions.
January 22, 2011 at 1:11 am
What does the usecounts column say? If it says 3 then the procedure was executed 3 times. If it says 1 then the procedure was executed once.
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
January 22, 2011 at 12:24 pm
GilaMonster (1/22/2011)
What does the usecounts column say? If it says 3 then the procedure was executed 3 times. If it says 1 then the procedure was executed once.
As you mentioned, usecounts would say how many times a proc was executed from cache, if you take alook in the excel sheet, the proc is executed three times in one set.
January 22, 2011 at 12:29 pm
Did you clear the proc cache before testing? If not, the proc could have been there from earlier executions
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
January 22, 2011 at 6:26 pm
GilaMonster (1/22/2011)
Did you clear the proc cache before testing? If not, the proc could have been there from earlier executions
No i did not, i cant clear because i am using sql 2005 edition and it will clear cache for all. Gail my total proc cache is just 3mb where as sql is assigned 43 gb, why is the proc cache usage so low? Am i missing something .
January 23, 2011 at 1:50 am
sqldba_icon (1/22/2011)
No i did not, i cant clear because i am using sql 2005 edition and it will clear cache for all.
You're testing on a dev or test server right? So what's the problem with flushing the cache of a dev or text server?
Gail my total proc cache is just 3mb where as sql is assigned 43 gb, why is the proc cache usage so low? Am i missing something .
Because SQL doesn't need more? I have no idea without having done an in-depth analysis of your system, usage patterns, restarts, etc
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
January 23, 2011 at 2:36 am
GilaMonster (1/23/2011)
sqldba_icon (1/22/2011)
No i did not, i cant clear because i am using sql 2005 edition and it will clear cache for all.You're testing on a dev or test server right? So what's the problem with flushing the cache of a dev or text server?
i did flush the cache
Gail my total proc cache is just 3mb where as sql is assigned 43 gb, why is the proc cache usage so low? Am i missing something .
Because SQL doesn't need more? I have no idea without having done an in-depth analysis of your system, usage patterns, restarts, etc
sure, i will provide some more data. i have learned new things about execution plans. thanks for your help.
January 24, 2011 at 7:12 am
sqldba_icon (1/20/2011)
I would like to know from other DBA's on how do they manage execution plans for procedures.i) i have a procedure when it is executed for the first time takes about 25 secs and 2 secs from there onwards. Now from the application side every morning there is slowness and then performance gradually improved. Do i need to worry if query is taking more than 25 secs to exec first time?
ii) is there a way to findout on how long will the execution plan stay in cache?
iii) can i force to stay execution plan for longer time in cache ? any pros /cons on this?
Thanks
I don't think your delay on first execution is due to compilation time. 99% of the time that extra wait occuring on first execution is because the DATA is being fed into RAM from the IO system - which can be VERY VERY SLOW. Second and subsequent executions hit the data straight from RAM and things are much faster. You eventually reach a steady-state balance where most frequently used data sits in RAM thus the "performance gradually improved" statement.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 24, 2011 at 7:37 am
sqldba_icon (1/20/2011)
... I have a procedure when it is executed for the first time takes about 25 secs and 2 secs from there onwards. Now from the application side every morning there is slowness and then performance gradually improved. Do i need to worry if query is taking more than 25 secs to exec first time?...
LOL! Yes, you need to worry. That user who always arrives first in the morning to get an early start is typically the one user who complains the loudest about occasional slowness in the application.
Other than optimizing the execution plans, one thing you could do is schedule a job to kick off the stored procedure once each morning at 6am, so the plan and page caches are freshly loaded when the first users start their day.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 24, 2011 at 7:39 am
As stated earlier... the most likely problem is the data cache. Unless having a 2000 lines proc with only 1-2 queries inside, the plan generation time will be futile compared to data retrieval.
January 24, 2011 at 12:09 pm
Eric M Russell (1/24/2011)
sqldba_icon (1/20/2011)
... I have a procedure when it is executed for the first time takes about 25 secs and 2 secs from there onwards. Now from the application side every morning there is slowness and then performance gradually improved. Do i need to worry if query is taking more than 25 secs to exec first time?...
LOL! Yes, you need to worry. That user who always arrives first in the morning to get an early start is typically the one user who complains the loudest about occasional slowness in the application.
Other than optimizing the execution plans, one thing you could do is schedule a job to kick off the stored procedure once each morning at 6am, so the plan and page caches are freshly loaded when the first users start their day.
Actually i thought of the same but is that how others do or just a temp fix?
January 24, 2011 at 12:53 pm
sqldba_icon (1/24/2011)
Actually i thought of the same but is that how others do or just a temp fix?
It shouldn't take 20+ seconds for SQL Server to compile an execution plan in the early AM, when most of the users havn't arrived, and the server is relatively idle. Instead it sounds to me like this stored procedure does some heavy duty page scanning, and pages from the previous exec have been cycled out of the page buffer cache overnight, resulting in a lot of physical page scans from disk for the first call rather than logical reads from memory for subsequent calls.
Take a stab at optimizing the query, if there are some obvious improvements to be made there. However, if the performance is acceptable after the first run of the day, then it might make sense to just have a job kick it off first in the AM, so the early bird user doesn't get anxious waiting for that initial form load. If the web app has to wait too long, it might even timeout on them, giving the appearance that there is something wrong with the application or database server.
Here is a link to a short article describing how the data buffer and plan cache are used. Also it contains a DMV query that returns a list of objects (tables, indexes, etc.) that are currently cached in the buffer, including page counts and total MB memory used.
http://www.sqlteam.com/article/what-data-is-in-sql-server-memory
For example:
name name type_desc Buffered_Pages Buffer_MB
------------------------------ ----------------------------------- ----------------- -------------- ---------
FORUM_REPLY IX_TOPIC_DATE CLUSTERED 13287 103
FORUM_TOPICS PK_FORUM_TOPICS CLUSTERED 6028 47
FORUM_MEMBERS PK_FORUM_MEMBERS CLUSTERED 2276 17
FORUM_REPLY IX_FORUM_REPLY NONCLUSTERED 1201 9
However, the script with that article will only list objects for whatever database currently has context. A few years ago, I was administrating a database server that had about 10 different databases, any combination of them joined in queries by the BI reports, so I took the script in the article above and retrofitted it to consolidate data from all databases on the server into one result. So this script below should give you a summary how your server's entire page buffer cache is allocated.
declare @sys_allocation_units table
(
database_idint,
allocation_unit_idbigint,
typetinyint,
type_descvarchar(20),
container_idbigint,
data_space_idtinyint,
total_pagesint,
used_pagesint,
data_pagesint
)
insert into @sys_allocation_units
EXEC sp_MSforeachDB @command1='use ?; select db_id(''?'') as database_id, * from sys.allocation_units'
declare @sys_partitions table
(
database_idint,
objnamevarchar(255),
partition_idbigint,
object_idbigint,
index_idint,
partition_numberint,
hobt_idbigint,
rowsbigint
)
insert into @sys_partitions
EXEC sp_MSforeachDB @command1='use ?; select db_id(''?'') as database_id, object_name(object_id) as objname, * from sys.partitions;'
declare @allocation table
(
database_idint,
objnamevarchar(255),
index_idtinyint,
allocation_unit_idbigint,
object_idbigint
)
insert into @allocation
SELECT au.database_id, objname, index_id ,allocation_unit_id, object_id
FROM @sys_allocation_units AS au
INNER JOIN @sys_partitions AS p
ON p.database_id = au.database_id and
au.container_id = p.hobt_id and
(au.type = 1 OR au.type = 3)
UNION ALL
SELECT au.database_id, objname, index_id, allocation_unit_id, object_id
FROM @sys_allocation_units AS au
INNER JOIN @sys_partitions AS p
ON p.database_id = au.database_id and
au.container_id = p.hobt_id and
au.type = 2
declare @bd table
(
database_idint,
allocation_unit_idbigint,
Buffered_Page_Countint,
row_countint
)
insert into @bd
SELECT
bd.database_id,
bd.allocation_unit_id,
count(*)AS Buffered_Page_Count ,
sum(bd.row_count) as row_count
FROM
sys.dm_os_buffer_descriptors AS bd
GROUP BY
bd.database_id,
bd.allocation_unit_id
SELECT --distinct
db_name(bd.database_id) as DBName,
obj.ObjName,
sum(bd.Buffered_Page_Count) as Buffered_Page_Count,
sum(bd.row_count) as row_count,
convert(numeric(9,2),sum(bd.Buffered_Page_Count) * 8192.0 / (1024.0 * 1024.0)) as Buffer_MB
FROM
@bd as bd
INNER JOIN @allocation AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id and
bd.database_id = obj.database_id
group by
db_name(bd.database_id),
obj.ObjName
order by
convert(numeric(9,2),sum(bd.Buffered_Page_Count) * 8192.0 / (1024.0 * 1024.0)) desc
--db_name(bd.database_id),
--obj.ObjName
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply