SQL 2005 Performance And Update Stats
Nov 6, 2007
Recently we moved few of our databases from SQL 2000 to SQL 2005 (SP 2) using backup and restore. After the restore I did Reindex and update stats on the databases. Since then we have observed performance issues on SQL 2005 databases but this performance problem vanishes the moment we run (sp_updatestats). Is this a problem with SQL 2005 that we have to run sp_updatestats 2 times a days or 3 times a day. In SQL 2000 we ran it only Once a week and still we never had any performance issues. Is there any config change we need to do to fix this problem in SQL 2005?
Thanks !
View 11 Replies
ADVERTISEMENT
Oct 23, 2007
I have client tools installed on a server and I have registered our 30+ instances hosted on various servers to this one MS SQL 2005 Management Studios.
Question:
How can I use this set up to send an e-mail distribution list a nice monthly chat showing the sizes of the database, memory, cpu utilization of all the registered databases?
Many thanks for your help !!
seethem
View 3 Replies
View Related
May 13, 2007
I have installed the reports for the performance dashboard and really like it!
However, I'd like to be able to clear the stats in order to run specific procedures etc and see the most inefficient parts in the specific procedure. The documentation says like this about it:
The lifetime of the DMV data depends on the lifetime of the plan in cache. You can determine how long the plan has been cached, and thus the time frame over which these resources have been consumed, by looking at the Plan Cached column
What Plan Cashed column? Can someone explain to me how to clear the stats?
Regards Andreas
View 3 Replies
View Related
Aug 11, 2004
Is it true that Update Statistics perform record commits if a query is outstanding?
View 4 Replies
View Related
Aug 8, 2007
Has anyone noticed a performance improvement during trading hours when they replaced sp_updatestats with UPDATE STATISTICS FULLSCAN in their nightly maintenance?
Or is it negligible?
View 6 Replies
View Related
Apr 17, 2006
SQL Server 2000 on Win2k
I'm fairly new to SQL Server and I'm just wondering if it's possible to Update Statistice for all indexes somehow? I'm looking at the Update Statistics command and it doesn't seem to be possible.
The situation we have is a reporting DB that basically has all it's tables truncated and remade every night by some DTS jobs that import from another datasource and change the data and build some denormalzed tables etc.
Some of the large Insert operations go from taking 8 mins to taking several hours sometimes and updating the stats seems to fix the problem for a while. So I'd like to make sure the optimizer has all the latest stats for all tables.
Any other advice would be appreciated.
Cheers.
View 2 Replies
View Related
Mar 10, 2003
Does anyone know how to tell how long it took for an auto update statistics to run? I looked under DBCC Show_Statistics and it shows the time the stats were last updated, but not how long it took to update them. Thanks.
View 2 Replies
View Related
Mar 12, 2015
I am having the following errors with the script below
Msg 102, Level 15, State 1, Line 44
Incorrect syntax near '?'.
Msg 319, Level 15, State 1, Line 47
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. it also does not seem to loop around each db
DECLARE @commandNVARCHAR(MAX)
CREATE TABLE #worktable
(
[Database]SYSNAME
,SchemaNameSYSNAME
,ObjectNameSYSNAME
,StatsNameSYSNAME
,ColNameSYSNAME
[code]....
View 6 Replies
View Related
Sep 26, 2005
(Assuming SQL Server 2000, auto create statistics on, auto updatestatistics on.)DoesDBCC DBREINDEX(<tablename>)update statistics? If yes, are the statistics equivalent to thosethat would be produced by:UPDATE STATISTICS <tablename> WITH FULLSCAN
View 1 Replies
View Related
Mar 10, 2003
Recently a production server suffered a critical blocking period and I wanted to know if I could solicit some input. It seems that a stored procedure was in the middle of recompiling while and auto update statistics started. This caused blocking for like an hour on the
single object (stored procedure) that was originally called. The table that the update occurred on and that the
stored procedure is reading form is quite large. It is 2 mil rows and about 140 columns wide. Some info from
sysprocesses is below. The table alone takes up almost 4GB of space, when looking at sp_spaceused. I have some
questions.
1. Can the update statistics for a '_WA%' stats cause
blocking on a table?
2. Does an update stats on an index survive a restart of
SQL server? We tried restarting, but the blocking did not
end.
3. If the stored procedure is running under a compile, can
the server automatically start an update stats and cause
the stored procedure to wait?
4. Can the server automatically start an update stats on
more than one column stats at a time, causing one to be
blocked by the other?
5. We had never seen this issue before going to SQL2K
clustering. Is this something specific to SQL2K and not
SQL7 ?
Thanks for your input.
John Lee
This is the lock info for the blocking processes.
spid dbid ObjId IndId Type Resource Mode Status name
------ ------ ----------- ------ ---- ---------------- -------- ------ -------------------------
142 7 2 1 KEY (6f00035ef42b) S GRANT sysindexes
142 7 2 1 KEY (6f00035ef42b) S GRANT sysindexes
142 7 421576540 0 TAB Sch-S GRANT tJob
142 7 1141579105 0 TAB Sch-S GRANT tPatient_info
142 7 1141579105 0 TAB [UPD-STATS] Sch-M GRANT tPatient_info
142 7 1659921035 0 TAB [COMPILE] X GRANT iDBGetPatInfoRecord
142 7 1659921035 0 TAB Sch-S GRANT iDBGetPatInfoRecord
These are the processes that are being blocked:
spid
------
137
140
Below this is a snapshot of all the SQL processes on the server being blocked.
Save the report and send to the whole database group.
spid kpid blocked waittype waittime lastwaittype waitresource
------ ------ ------- -------- ----------- -------------------------------- -----------------------------
140 4292 142 0x0005 68609 LCK_M_X TAB: 7:1659921035 [[COMPILE]]
137 2576 140 0x0005 64671 LCK_M_X TAB: 7:1659921035 [[COMPILE]]
View 1 Replies
View Related
Jul 22, 2004
Microsoft states that dbcc DBREINDEX automatically updates statistics but INDEXDEFRAG does not. If this is the case, does MS mean that only the affected statistics are updated or all statistics? Also, is it a good idea to run 'Update Statistics' after doing INDEXDEFRAG?
Clive
View 1 Replies
View Related
Feb 17, 2012
How do i update the stats of tables when we insert data into it. I believe Auto stats update happens only when 500+ 20% of the rows are changed for a table. Once we insert say some 1000 records in to a particular table the query time takes too long (more than 1 min). The same query executes faster once i manually update the stats.
View 3 Replies
View Related
Jul 22, 2014
I am using Ola Hallengrens scripts for index and stats maintenance but I am wondering what most people to in terms of the maintenance schedules. At present we do an index rebuild reorg weekly, but do people also do update stats nightly?
I suppose there is an element of "it depends" here in that the data may be fairly static so the update stats may not be required, or if heavily updated then perhaps rebuilding indexes may be required more frequently.
View 5 Replies
View Related
Mar 24, 2015
We have a file import job. This job typically imports millions of records into a SQL2008 DB. After the load the DB performance goes down the drain. Thus far, their solution has been to rebuild indexes on effected tables. I'd like to come up with a better solution. My guess is that after the load, the statistics are shot until the next stats update.
What is the best way to handle this scenario? There must be some way to keep the stats current during a big data load.
View 3 Replies
View Related
Sep 28, 2015
We are planning to standardize our newly deployed sql server, As a part of it we have configured 2 maintenance plans 1) Update Statistics which runs daily and 2) Index Reorganize which runs on weekly.
Apart from above, any other things to be in place for better maintenance of the sql server.
Also, how to Index Rebuild activity for clustered indexes requires any downtime.
View 10 Replies
View Related
Apr 9, 2014
I noticed today a session that was executing a FULL SCAN update as follows:
UPDATE STATISTICS [XXXX].[XXXX].[XXXX] [_WA_Sys_00000009_318D45CA] WITH FULLSCAN
When I checked the sys.dm_exec_query_memory_grants DMV for the session I could see the following values:
requested_memory_kb granted_memory_kb used_memory_kb max_used_memory_kb
145,705,216 145,705,216 139,977,336 139,980,408
When I checked the Properties of the Statistic I can see it is on a varchar(3) field when there are only 3 different values in there - all char(1)
The total size of the data in the table according to the Disk Usage By Top Table Report is 199,680,712KB
So my question is this...
For the UPDATE STATS on this one column with FULL SCAN, does SQL Server read the entire table into the Buffer Pool. If so then if the table had 199,680,712KB of data then why did the session request 145,705,216KB.
Or does SQL Server just read the column and ClusteredIndex Key into the Buffer Pool?
View 1 Replies
View Related
Mar 18, 2015
I have a job that runs nightly, rebuild index. job runs fine every night but every few weeks it fails.
USE msdb
GO
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@SortInTempdb = 'Y',
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'
This calls the Sp that does the Reindex. It fails at the update statistics with a very generic message. like " Command: UPDATE STATISTICS [xxxx_DB].[dbo].[xxxx_xxx] [_WA_Sys_00000007_49C3F6B7] [SQ... The step failed."
I suspect it has more error but this is all it is showing me when I right click on the job history. therefore, I updated the job step in the advance tab with log to a txt file. Am I on the right track or there is another way to see error some where else.
I looked at the logs but they didn't show any thing.
View 9 Replies
View Related
Oct 25, 2007
I installed the SQL Server 2005 SP2 update 2 rollup on my 64-bitserver and the performance has tanked!I installed rollup 3 on some of them, but that did not seem to help.I thought it was just a linked server performance issue, but myoptimization started running today on one of the "update 2" instancesand so far it's been running about 10 hours longer than it normallydoes.The rollup 3 fixed our stack dumping issues, but we NEED to have thisperformance thing fixed!I saw that MS has come out with update 4 last week - doesn't sayanything about fixing this, though.Has anyone else experienced this?I'm not necessarily expecting anyone to have a fix for this, justwantto know I'm looking in the right place before I call MS.
View 3 Replies
View Related
Jun 26, 2015
We face slow performance issue for like taking long time for same query execution after We apply index rebuild and reorganize index. But, after execution of query or procedure for 2 -3 times, performance will be faster. I have following questions
1 do we need to update stats after we rebuild an reorganize index.
2. is it will be slow for 1-2 times for every query and stored procedure execution after we rebuild and reorganize index?
View 2 Replies
View Related
Jun 15, 2015
We have recently migrated quite a databases around 20 from SQL 2000 and 2005 to SQL server 2008R2.
I am using Ola's script for index maintenance for those with compatibility level above 80 as i heard it supports that way.
Hence separated in 2 way job where for those with compatibility level 80, we are running job with below query on each database with 80 as compared
USE ABC
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
I am not sure if this is the only way in for those databases, because we are seeing the database getting because of that somewhere using above query.( seems log file filling very rapidly).
But above is not the case with those databases , with compatibility 90 and above.
View 5 Replies
View Related
Oct 28, 2015
My SSIS package is running very slow taking so much time to execute, One task is taking 2hr for inserting 100k records, i have disabled unused index still it is taking time.I am rebuilding/Refreshing indexes and stats once in month if i try to execute on daily basis will it improve my SSIS Package performance?
View 2 Replies
View Related
Dec 29, 2007
The auto stats not working
I have both Auto Update Statistics and Auto Update Statistics Asynchronously set to True
Created a little test table.
USE [TEST]
GO
/****** Object: Table [dbo].[CUSTOMER] Script Date: 12/29/2007 10:42:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CUSTOMER](
[Customer_Id] [nchar](10) NOT NULL,
[Customer_Name] [nvarchar](1000) NULL,
[Customer_Address] [nvarchar](1000) NULL,
[Customer_Address1] [nchar](1000) NULL,
CONSTRAINT [PK_CUSTOMER] PRIMARY KEY CLUSTERED
(
[Customer_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
--Created an insert table
DECLARE @COUNT INT
SET @COUNT = 1
WHILE @COUNT <= 1000
begin
insert into CUSTOMER (CUSTOMER_ID, CUSTOMER_NAME)
VALUES (@COUNT, '12345678901234567890')
SET @COUNT = @COUNT + 1
END
I then look at Tables then statistics the statistics are empty so i fire update statistics and see 1000 rows in here.
I run again the insert script
DECLARE @COUNT INT
SET @COUNT = 1001
WHILE @COUNT <= 2000
begin
insert into CUSTOMER (CUSTOMER_ID, CUSTOMER_NAME)
VALUES (@COUNT, '12345678901234567890')
SET @COUNT = @COUNT + 1
END
Look again at statistics it does not have 2000 rows in here.
If i do select * from CUSTOMER where CUSTOMER_ID = '2000' then go checks statictics it works.
I was under the impression that when you do insert or delete, update then the statistics are fired.
The sys.sysindexes rowmodctr shows the 1000 rows.
I checked the conditions that sql fires if the no of rows int able > 6 and < 500 then updates when 500 mods made.
Also if row > 500 auto update done when 500 = 20% are added
So both are met.
Anyone other any other suggestions about the auto stats ?
View 7 Replies
View Related
Dec 29, 2007
The auto stats not working
I have both Auto Update Statistics and Auto Update Statistics Asynchronously set to True
USE [TEST]
GO
/****** Object: Table [dbo].[CUSTOMER] Script Date: 12/29/2007 10:42:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CUSTOMER](
[Customer_Id] [nchar](10) NOT NULL,
[Customer_Name] [nvarchar](1000) NULL,
[Customer_Address] [nvarchar](1000) NULL,
[Customer_Address1] [nchar](1000) NULL,
CONSTRAINT [PK_CUSTOMER] PRIMARY KEY CLUSTERED
(
[Customer_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
--Created an insert table
DECLARE @COUNT INT
SET @COUNT = 1
WHILE @COUNT <= 1000
begin
insert into CUSTOMER (CUSTOMER_ID, CUSTOMER_NAME)
VALUES (@COUNT, '12345678901234567890')
SET @COUNT = @COUNT + 1
END
Look at Tables then statistics the statistics are empty so i fire update statistics and see 1000 rows in here.
I run again the insert script
DECLARE @COUNT INT
SET @COUNT = 1001
WHILE @COUNT <= 2000
begin
insert into CUSTOMER (CUSTOMER_ID, CUSTOMER_NAME)
VALUES (@COUNT, '12345678901234567890')
SET @COUNT = @COUNT + 1
END
Look again at statistics it not firing.
If i do select * from CUSTOMER where CUSTOMER_ID = '2000' then go checks statictics it works.
I was under the impression that when you do insert or delete, update then the statistics are fired.
The sys.sysindexes rowmodctr shows the 1000 rows.
I checked the conditions that sql fires if the no of rows int able > 6 and < 500 then updates when 500 mods made.
Also if row > 500 auto update done when 500 = 20% are added
So both are met.
Anyone other any other suggestions about the auto stats ?
View 2 Replies
View Related
Jan 23, 2008
Hi,
My friend expresses his concern over the performance of his update query.
He says the execution time takes around 120 ms.
the query is looking like
Update table set col1 = 'value', col2 = 'val', col3= 'val'............ col40='val'
where col1 = 'value' and col2 = 'value' and col3 = 'val' and col4 = 'val'
He says he's updating around 40 columns at a stretch with filter conditions as shown, and only 1 row is getting affected by this.Where the col1, col2,col3 n col4 are composite primary keys and have default clustered indexes created on them.
Is that the execution time he say is really a performance issue ? I'm confused on this
Experts, pls post your comments and any tips on improving update query in general will immensely help.
thanks,
View 3 Replies
View Related
Oct 20, 2002
I should add an Identity field (Identity=True) and a row version field(timestamp) to my table, and avoid to arrange tables into different databases, is it true in general?
View 4 Replies
View Related
May 8, 2006
Hi,I have an UPDATE statement which runs very slow (about 1-2 minutes) inWeb as well as in Query Analyzer. Very plain UPDATE statement; usesindexes, etc. I though the reason might be the table being unorganizedbecause of the row expansions due to updating an existing column valuewith a larger value. Therefore, I ran a maintanenance plan whichreorganizes data and rebuilds indexes. But, it seems that there is noimprovement.Any thought or advise will be greatly appreciated.Sincerely,Pelin Bali.
View 1 Replies
View Related
Jun 26, 2007
Hello!I have this piece of SQL code:UPDATE aSET Field1 = c.Field1FROM aINNER JOIN b ON a.GUID1 = b.GUID1INNER JOIN c ON b.GUID2 = c.GUID2WHERE c.Type = 1AND @date BETWEEN b.DateFrom AND b.DateToThis query takes hours to complete.Now while trying to find out what's causing the poor performance (itsurely looks simple enough!) I've rewritten it to use temp tables:SELECT a.GUID1, a.Field1, c.Type, b.DateFrom, b.DateTo INTO #temptableFROM aINNER JOIN b ON a.GUID1 = b.GUID1INNER JOIN c ON b.GUID2 = c.GUID2WHERE c.Type = 1AND @date BETWEEN b.DateFrom AND b.DateToUPDATE a SET Field1 = subsel.Field1FROM (SELECT * FROM #temptable) AS subselWHERE subsel.GUID1 = a.GUID1Now it completes in 10 seconds.My question is why? Am I wrong in saying that the two batches aboveproduce same results? Is there something I've missed about the UPDATEFROM syntax? Why would the first query perform THAT poorly?Table sizes:a: 24k rowsb: 268k rowsc: 260k rowsGUIDs are of type uniqueidentifier.Any answers appreciated!Regards,// Richard
View 8 Replies
View Related
Jul 20, 2005
Dear Sql Server experts:First off, I am no sql server expert :)A few months ago I put a database into a production environment.Recently, It was brought to my attention that a particular query thatexecuted quite quickly in our dev environment was painfully slow inproduction. I analyzed the the plan on the production server (itlooked good), and then tried quite a few tips that I'd gleaned fromreading newsgroups. Nothing worked. Then on a whim I performed anUPDATE STATISTICS on a few of the tables that were being queried. Thequery immediately went from executing in 61 seconds to under 1 second.I checked to make sure that statistics were being "auto updated" andthey were.Why did I need to run UPDATE STATISTICS? Will I need to again?A little more background info:The database started empty, and has grown quite rapidly in the lastfew months. One particular table grows at a rate of about 300,000records per month. I get fast query times due to a few well placedindexes.A quick question:If I add an index, do statistics get automatically updated for thisnew index immediately?Thanks in advance for any help,Felix
View 17 Replies
View Related
Jul 20, 2005
hiFor an unavoidable reason, I have to use row-by-row processing(update) on a temporary table to update a history table every day.I have around 60,000 records in temporary table and about 2 million inthe history table.Could any one please suggest different methods to imporve the runtimeof the query?Would highly appreciate!
View 9 Replies
View Related
Jul 20, 2005
Below is a simple UPDATE that I have to perform on a table that hasabout 2.5 million rows (about 4 million in production) This queryruns for an enourmous amount of time (over 1 hour). Both theChangerRoleID and the ChangerID are indexed (not unique). Is thereany way to performance tune this?Controlling the physical drive of the log file isn't possible at ourclient sites (we don't have control) and the recovery model needs tobe set to "Full".UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID WHEREChangerRoleID IS NULLAny Help would be greatly appreciated!
View 2 Replies
View Related
Apr 23, 2008
Hi All,
Not sure if this question belongs in this area or the server area but I'll start here. Here is my problem. We have an C/C++ app that was originally written for SQL 2000 and uses DBLibrary. We have converted it to SQL 2005 and are using ODBC/Native client to access the SQL 2005 database. This all works great. So we were doing some performance testing and we noticed that our update performance seems slower in the SQL 2005/ODBC case than it did in the SQL 2000/DBLibrary case. Inserts and queries all perform great, in fact the inserts are significantly faster in the SQL 2005/ODBC case which is good. We are using Array inserts/updates/queries wherever possible as this is faster obviously. In our update case, it takes 1.14 seconds to update 2000 rows in table in the SQL 2005/ODBC case, while SQL 2000/DBLibrary case takes .39 seconds to the exact same thing. The table in question is a 12 column table with all integer columns, with an index on the first three columns.
So my question is this. Is there something different about Array Updates in SQL 2005 ? I've looked thru the list of hot fixes available since SQL 2005 SP2 and haven't seen anything that directly mentions Updates so I'm hesitant to go off and start applying the hot fixes to our server to see if the behavior changes. It seems strange to me that Array Inserts would be very fast, but Updates wouldn't be. I've checked the ODBC Data Source and we aren't doing anything fancy there. I'm not actually even sure if this problem is client side or server side as I said earlier.
If anyone has any ideas or thoughts that would be great since this is really bugging me.
Here is a sample of what our C code is doing. This is simplied and I've removed a lot of our own code but these are the SQL calls that we are making in order so maybe can see something wrong I'm doing.
//----Sample Code -------------------------------------------------------------------------------------
pSQL = "Update bob set VV=? where VI=?" // not done this way actually in our code but just to show the update text
status = SQLPrepare ( hS, (UCHAR *)pSQL, SQL_NTS );
// Called twice with nCol = 1 and then with nCol = 2
status = SQLSetParam (
hS,
abs(nCol),
cType, // cType = 5 = SQL_C_LONG
sqlType, // sqlType = 4 = SQL_INTEGER
38, // size needed in case the column is numeric or decimal
0,
p16Data, // Pointer to my array of data
NULL
);
SQLSetStmtAttr(hS, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)numrows, 0); // numrows = 2000
SQLSetStmtAttr(hS, SQL_ATTR_PARAMS_PROCESSED_PTR, &p16cRows, 0);
status = SQLExecute (hS);
SQLParamOptions(hS, 1, NULL);
SQLFreeStmt ( hS, SQL_RESET_PARAMS );
//----Sample Code -------------------------------------------------------------------------------------
Thanks,
Nick
View 5 Replies
View Related
Oct 2, 2007
If I run sp_updatestats, does it effect performance?
My testing also reveals canceling sp_updatestats, doesn't caus a rollback?
View 4 Replies
View Related
Oct 16, 2013
I need to figure out the correct update statement syntax for the following integration.
I have a "Performance Table" which i insert weekly performance numbers into for each store. The table is constructed w/ columns such as Store, Weekenddate, Sales, Refunds, #ofPatients
In a "Averages Table" i have every weekenddate for each store populated. So 52 Weeks for 10 stores = 520 Rows of Store numbers & WeekendDates.
What i would like to do is run a loop or update statement which would update the store average for each weekendate based on the last 13 weeks.
This is my query
update performancestore_avgs set SalesAvg =
(select sum(SalesHit)/Count(Store) from performance_store where performance_store.weekenddate >= performancestore_avgs.weekenddate-84 and performancestore_Avgs.store = performance_store.store)
The update statement runs but the averages are completely wrong.
View 3 Replies
View Related
