November 20, 2009 at 11:01 am
Hi every one,
I have a query which used to take 13 minutes to run then i have created indexes and now it is taking less than 1 minute(it is varying in time i.e sometimes it takes 24 sec some times 40 sec like that)which is good.but after creating indexes when i ran the query for the first time it took 6 min and after that onwards it is taking less than 1 min or 1 min.
I know that for the first time it really takes time to cache the plan .but my manager is not getting convinced.please tell me if i am wrong . The query i am talking about is the backend of the Business objects reports and i have also posted about the query,executionplan and indexes in recent performance issue topic....
My next Question is we are planning to rebuild the indexes and schedule it because the data is getting updated daily .now,do i need to rebuild the statistics too ?i am afraid because updating statistics will decrease the performance .
how can i automate the statistics?
Thanks for your help
November 20, 2009 at 11:49 am
Use Books On Line:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ac7e4c7c-e52f-4883-8f3c-9336cc77a9c8.htm
And it will show you how to handle auto-statistics creation and updatting. Both Create Auto statistics and update auto statistic is by defaul set to ON when the database is created, however these settings could have been altered by someone at some unknown time. So check them,.
November 20, 2009 at 11:58 am
It is normal for a query to take longer on the first run until the plan is cached.
Rebuild statistics is not always necessary as part of the rebuild indexes maintenance.
And for the automatic update of statistics, see bitbuckets reply.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 20, 2009 at 1:50 pm
Thank you for the reply .
I have come across few articles which tells me when we should rebuild the index using fragmentation and DMV.
Using DMV i.e
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'), NULL, NULL, NULL , NULL);
I got the error like
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.
But, using DBCC SHOWCONTIG i.e
USE AdventureWorks;
GO
DBCC SHOWCONTIG ('HumanResources.Employee')
GO
I got the results :
DBCC SHOWCONTIG scanning 'tb_ForecastAgg' table...
Table: 'tb_ForecastAgg' (565577053); index ID: 0, database ID: 14
TABLE level scan performed.
- Pages Scanned................................: 4566
- Extents Scanned..............................: 579
- Extent Switches..............................: 578
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 98.62% [571:579]
- Extent Scan Fragmentation ...................: 3.45%
- Avg. Bytes Free per Page.....................: 3036.6
- Avg. Page Density (full).....................: 62.48%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Here ,where is the Internal fragmentation? and i believe extent scan fragmentation is external fragmentation ,please correct me if i am wrong.
Why am i not getting results when i use DMV query:
Can anybody tell me how to write query to rebuild indexes depending on fragmentation.
Thank you
November 20, 2009 at 2:17 pm
Two things..
1) DMV Not working...
I have copied and pasted your code in Query window and it executed successfully. may you have executed by highliting the query and leaving the paranthesis..try again..
2) DBCC Showconting..
Understanding Logical Scan Fragmentation and Extent Scan Fragmentation values.
Logical Scan Fragmentation and, to a lesser extent, Extent Scan Fragmentation values give the best indication of a table's fragmentation level. Both these values should be as close to zero as possible (although a value from 0% through 10% may be acceptable). It should be noted that the Extent Scan Fragmentation value will be high if the index spans multiple files. Both methods of reducing fragmentation can be used to reduce these values.
--Jus
November 20, 2009 at 2:58 pm
Still getting the same tried many times .
Do i need to create the function before i select sys.dm_db_index_physical_stats....
Thanks fo your reply.
November 20, 2009 at 4:39 pm
srilu_bannu (11/20/2009)
Still getting the same tried many times .Do i need to create the function before i select sys.dm_db_index_physical_stats....
Thanks fo your reply.
You don't need to create any function.I too copied the same script that you have mentioned and was able to run successfully.
Try opening a new query and see running only this query?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 20, 2009 at 4:56 pm
srilu_bannu (11/20/2009)
SELECT *FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'), NULL, NULL, NULL , NULL);
I got the error like
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.
This sounds crazy to myself don't know about others.
Have you tried looking at what Version of SQL Server you are using wherever you are running this Select DMV statement.
First, by looking at your code which has Adventureworks, I had no doubt that you are running SQL 2005 or a higher version. Since DMV's are introduced in SQL 2005 it would not work in SQL 2000.
Then I tried connecting to a SQL 2000 Server and run the query against another database, I got the same error with Msg No 170 instead of 102.
I used the following code
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID('pubs'), NULL, NULL, NULL , NULL);
My Error was
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Just wanted to share here so that you would double check your SQL version.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 22, 2009 at 4:39 pm
I have installed sql server 2005 standard edition.but the compatibility is sql server 2000(80).
I have only selected DB_ID and ran the query i got this:
Select DB_ID('oncx')
Result:
Nocolumnname
14
And i tried this :
SELECT *
FROM sys.dm_db_index_physical_stats(14, NULL, NULL, NULL , NULL);
I got the results with Database_ID =14;
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply