Calculating The Time Required To Update Statistics

Mar 6, 2008

We have a proc that runs nightly which updates stats. For each database it will run dbcc updateusage on each table, then then sp_updatestats on the database itself.

This process normally takes about an hour, but recently the time has gone up dramatically.

I am looking at ways of making the run faster. One suggestion was to split the work and have it run in parrallel, with multiple procs all doing the same job but on different databases. So for example, I could have a proc that would determine which databases would take the longest to process, and then call 3 different copies of our update proc, each getting a different set of databases to process.

If I went this route, how could I determine which databases would take the longest to complete this process? I'm investigating determining this from sysindexes' rowmodctr, or maybe just database size?

Any suggestions on how I could get an estimate of which dbs would be the most costly to update stats? This is for SQL Server 2000.

Thanks

View 3 Replies

Unit Of Time-statistics In Client Statistics

Aug 1, 2006

What is the unit of the numbers you get in the Time Statistics-part when running a query in Microsoft SQL Server Management Studio with Client Statistics turned on?

Currently I get mostly 0�s, but if I try and *** up a query on purpose I can get
it up to around 30... Is it milliseconds or som made up number based on clockcycles or... ?

I would also like to know if it�s possible to change the precision.

- Nikolaj

View 3 Replies View Related

Calculating Positions Query Required...........

Aug 31, 2006

AA Guyz i want to calculate class position of students from a table.Sample data is as follows...Roll # - Name - Marks1 - ABC - 602 - DEF - 603 - GHI - 574 - JKL - 555 -MNO - 506 -PQR - 53The query should return the following result.Roll # - Name - Marks - POSITION1 - ABC - 60 - 12 - DEF - 60 - 13 - GHI - 57 - 34 - JKL - 55 - 45 -MNO - 50 - 56 -PQR - 53 - 6I want query in MS SQL Server 2000

View 1 Replies View Related

Profiler Vs SET STATISTICS IO / TIME

Sep 2, 2004

This morning i was profiling two queries to see if the changes i've done were improving it.

So i firstly used the usual SET STATISTIC IO ON.

This is the result on the First and the Second queries:

(681 row(s) affected)

Table 'WEB_NewsletterSubscription'. Scan count 2724, logical reads 5452, physical reads 0, read-ahead reads 0.
Table 'WEB_ProductRegistration'. Scan count 2724, logical reads 5456, physical reads 0, read-ahead reads 0.
Table 'WEB_Journalist'. Scan count 2724, logical reads 5456, physical reads 0, read-ahead reads 0.
Table 'WEB_User'. Scan count 4, logical reads 292, physical reads 0, read-ahead reads 0.

(681 row(s) affected)

Table 'WEB_NewsletterSubscription'. Scan count 2076, logical reads 4335, physical reads 0, read-ahead reads 0.
Table 'WEB_ProductRegistration'. Scan count 2043, logical reads 4231, physical reads 0, read-ahead reads 0.
Table 'WEB_Journalist'. Scan count 2043, logical reads 4092, physical reads 0, read-ahead reads 0.
Table 'WEB_User'. Scan count 3, logical reads 219, physical reads 0, read-ahead reads 0.

From what I see i say that the Second is better.

So switched to profiler to be ever sure, and got this:

Event ClassCPUReadsWritesDur.
SQL:BatchCompleted SQL Query Analyzer******100418901301948572004-09-02 12:02:38.493
SQL:BatchCompleted SQL Query Analyzer******150448601701948572004-09-02 12:02:38.623

WHAT!??!
Now seems that the First is better!!!!

Who's joking me? Why two different reponses? Who is more reliable?

View 2 Replies View Related

Calculating Time

Aug 27, 2005

Hello,
I am fairly new to this, but I need to write a query that calculates whether an order made a shipping cutoff based on creation time, and when it actually shipped, based on local customer time. All times in the table below are central time.
Heres where it gets complicated for me. If the order was created after 8:00am local customer time the same day, and shipped prior to 5pm local customer time, it would be considered a pass, or a 1. If an order was created after 8am local customer time, BUT didnt ship until AFTER 5pm local customer time, it would be considered a failure or a 0. And to complicate things even more, if the order is created after 5pm local customer time, the order create time would be treated as it was created at 8am local customer time the following day local customer time, and would need to be shipped prior to 5pm local customer time that day. Would CASE be appropriate for this? Or am I thinking wrong? Any help would really be appreciated!!

Ordernum OrderCreate OrderShipDate TimeZ
67890 5/5/2005 11:575/6/2005 15:33 EST
35789 5/5/2005 13:575/5/2005 14:19 MST
44465 5/5/2005 13:58 5/5/2005 21:58 CST
87654 5/5/2005 18:00 5/7/2005 21:58 PST

View 13 Replies View Related

Need Help Interpreting Results Of SET STATISTICS TIME ON

Dec 14, 2007

Hi,

I used SET STATISTICS TIME ON to get execution stats for a query. I found that the CPU Time was sometimes greater than the elapsed time. How is this possible? The query does not use any parallelism since I used the query option MAXDOP 1. Is the elapsed time wait time? Is the total execution time the sum of the CPU time and elapsed time?

SQL Server Execution Times:

CPU time = 797 ms, elapsed time = 162 ms.

View 3 Replies View Related

Help With Calculating Duration Time

Oct 14, 2005

I have a table called Tickets which contains ticket information for a machine. Each machine can have more than one ticket number opened at the same time. The ticket number contains start date/time and end date/time of the ticket. Thereefore the table looks something like this:

Ticket_No (int)
Machine_No (int)
Description (char)
Start_Time (datetime)
End_Time (datetime)

I want to be able to calculate total duration time(in hours) that EACH MACHINE had a ticket open...but here is the tricky part. The total duration time that a machine had ticket open has to encompas any tickets that may fall in the same time period. For example:
If Machine A has a ticket open at 8:30 and the ticket is closed at 10:00. Meanwhile, Machine A had another separate ticket open at 9:30 which was closed at 10:30. In this case, the total duration time for this machine would be from 8:30 to 10:30 for a total of 2 hrs duration time.

Can anyone help me get started in tackling this problem or provide any examples?

View 4 Replies View Related

Calculating Time Between Each Record?

Dec 4, 2013

Code:
CREATE TABLE [dbo].[DigiTracker](
[DigitrackID] [int] IDENTITY(1,1) NOT NULL,
[contact_id] [int] NOT NULL,

[Code]....

-- not accurate b/c it returns 0 for all TimePerPageSec
SELECT DATEDIFF(second,min(createDt),max(createDt)) AS TimePerPageSec
FROM DigiTracker
GROUP BY contact_id, ZinePageNumber
time spent (in seconds) between each record

View 9 Replies View Related

Calculating Duration Time

Dec 13, 2007

View 2 Replies View Related

Calculating Time Difference

Jul 20, 2005

Hi,I have a table called Bookings which has two important columns;Booking_Start_Time and Booking_End_Time. These columns are both of typeDATETIME. Given any day how can I calculate how many hours are availablebetween the hours of 09.00 and 17.30 so a user can see at a glance how manyhours they have unbooked on a particular day (i.e. 8.5 hours less the timeof any bookings on that day), can this be done with a queryor do I have to work it out in my code?Thanks for your help

View 2 Replies View Related

I Need Help In Calculating Time Duration

Dec 13, 2007

View 6 Replies View Related

Update Statistics

Feb 6, 2001

I am using SQL 6.5. We have tables of upto 2.5GB in size. Running update statistics againts these tables takes too long, and locks out users.

Anyone know how I can make this quicker?

Davy

View 4 Replies View Related

Update Statistics

Dec 9, 2004

Hi,

I am contemplating creating a job to execute every 5 mins which will update index statisics if they are more than say 8% out. I would like to know what thoughts people have on this? i.e. pros and cons.

I like forward to what you have to say.

I have auto stats on. Our stats are often more than 10% out. At what level do you reckon the query plan might be effected by out of data stats?

Thanks
Jamie.

View 5 Replies View Related

Update Statistics

Dec 7, 2005

Is the Update Statistics found in a Maintenance Plan performed online or offline? Will it kick users out when this is run on SQL Server 2000?

Thanks

View 1 Replies View Related

Statistics - How To Update

Sep 6, 2006

Hi,

It seems to me there are many ways to update statistics for a table. i.e. "sp_updatestats", "sp_recompile", "dbcc updateusage"

Can somebody tell me the difference between those commands and what's the best way for updating your statistics? Does reindexing update the statistics?

thx,
Wilfred van Dijk

View 3 Replies View Related

UPDATE STATISTICS

Dec 14, 2007

HI,

To update statistics for entire DB i have taken the script from under given link.But need to know the
1 : what is sample percent on update statistics
2 : will it be applicable for 2005 ?

script taken from :
http://weblogs.sqlteam.com/tarad/archive/2006/08/14/11194.aspx

kindly reply soon.

View 1 Replies View Related

Update Statistics

Feb 26, 2008

How do you schedule a weekly job to do "update statistics" thru all the databases?

View 6 Replies View Related

Update Statistics

Dec 5, 2007

Hi All,
I update statistics for three tables every day 2:00 AM and in the job we call
one stored procedure and, in that stored procedure only three statements are
writtern for update statistics
Like:
Exec('update statistics TBL1 with fullscan')
Exec('update statistics TBL2 with fullscan')
Exec('update statistics TBL3 with fullscan')
And this job was working fine since many months but last two days its getting fail and it gives the error messages like :
could not continue scan with NOLOCK due to data movement
So could you help me what is the solution for this

Thanks,
Aric

View 1 Replies View Related

Update Statistics

Jul 23, 2007

Hi,

I would like to know when we upgrade SQL Server 2000 database to SQL Server 2005 is it required to update the statistics even if we rebuild all the indexes or create new indexes?

Thanks,

Ritesh

View 5 Replies View Related

UPDATE STATISTICS

Mar 12, 2008

Hi,

I am planning to change our current UPDATE STATISTICS strategy, which is auto stats ON. Our database is terrabytes sizes and some tables with millions of rows with over 200 indexes in one table. Some of these indexes are not really used. Most of the tables are very small.

Droping and creating new indexes are quite often used in our environment. So static script may not help.

How can I identify most frequently used indexes in a table?

With the Microsft recommended auto stats ON, what are the best other practices I can include to improve the effeciency?

Any help would be apprecited. It would be realy great if any of you can share some scripts to generates dynamic scripts.

best regards

Priw

View 3 Replies View Related

Calculating Time Difference Between Two Different Dates

Dec 9, 2011

I have data in which i need to calculate employees working hours for a day...

name time in_out
manisha 2011-01-01 9:30:00.000am 1
manisha 2011-01-01 10:30:00.000 0
manisha 2011-01-01 10:45:00.000 1
manisha 2011-01-02 1:00:00.000am 0

How can i calculate time in that two dates as 1 is for entry an 0 is for exit..

View 2 Replies View Related

Calculating The Time Duration For Each Event

Jun 6, 2008

Hi all,

Thanks for setting up such a great site and forum.

Here is my problem:

I have a table like the following in SQL Server 2005:

order | taskid | main_person | temp_person | start_assign_date
1 | 3 | John | John | 2008-01-01 10:20:22
2 | 3 | John | Joe | 2008-02-05 15:20:22
3 | 3 | John | John | 2008-02-07 20:25:20
4 | 6 | Joe | Joe | 2008-01-01 10:20:22
5 | 6 | Joe | Mike | 2008-02-01 10:20:22
6 | 10 | Doug | Doug | 2008-01-01 10:20:22
7 | 7 | Russ | Russ | 2008-02-01 11:20:22
8 | 7 | Russ | Mike | 2008-02-08 12:20:22
9 | 7 | Russ | John | 2008-02-10 20:05:12

It was made to record who was in charge of a specific task at a specific time. Each task has its own main responsible person and some substitutes for that person as Temporary Persons (who did the task while main person was away). The Main Person's name is in the temp_person column when he is doing the task by himself.

I'd like to generate a report that shows:
- in a specific time period
- which persons were in charge of a specific task and
- for how long

Something like this:

From 2008-##-## to 2008-##-##
Task 3 - John - 15 days
Task 3 - Joe - 5 days
Task 6 - Joe - 18 days
Task 6 - Mike - 2 days
Task 10 - Doug - 20 days

I have some ideas to do that when there are both start and end dates for every record but I couldn't find a way to use the next assignment start date for each task, as the end date for its previous record (in that task group) to calculate the duration for that record.

I can group the tasks and users and put them in the chronological order but I can't indicate the next start date as the end date for the previous record (in specific task group) to use the date difference functions.

Any hint or comment would be appreciated.

Thanks
Sami

View 4 Replies View Related

Calculating Query Running Time...

Dec 19, 2007

Hi all
As you know when you run a piece of TSQL scrypt in Query Analizer
at the bottom of the page , sql-server will show you the Time of completion of your code....is there any way to capture this time
from SQL-Server environment and use it in the Front-End Application
to inform the user?

Kind regards.

View 2 Replies View Related

Calculating 'time Difference' Between Two Records....

Dec 21, 2005

I have a data set like so:UTC_TIME Timestamp NodeID Message FlagLineStation11/19/2005 10:45:07 1132397107.91 1 3 5 1028103411/3/2005 21:05:35 1131051935.20 2 3 5 1009104311/25/2005 21:12:16 1132953136.59 3 3 5 10371049I added the UTC_TIME column in as aconversion of the unix timestamp inthe TIMESTAMP column.Keeping things simple and straightforward, I need to be able tocalculate the difference from one record to the next (ordered byTIMESTAMP or UTC_TIME) and output the result into another column in thetable.NODEID is the unique id.First, what is the function to do so if, say, I only wanted tocalculate the difference between 2 records as just a basic SELECTstatement. That way I can answer quick question based on any one or twoNODEID's.Second, how would I further that to continually calculate (as statedabove)?WOuld this be a stored procedure? A trigger? A cursor?I am learning as I go here. Any help is greatly appreciated.R.

View 4 Replies View Related

Transact SQL :: Calculating Transaction Time

Nov 23, 2015

select TOP 10 rec_id,trans_id,number_id,card_no,message_id,trans_datetimefrom [dbo].[trans_log]
order by trans_datetime desc101, 1,34343, 99999, 200, 2015-11-23 12:27:25.710101,2,34343,99999,210,2015-11-23
12:27:26.710102,3,43434,88888,200,2015-11-23 12:28:26.714102,4,43434,88888,233,2015-11-23 12:28:27.710expected result:34343,99999,datediff(ss,'2015-11-23 12:27:26.710','2015-11-23 12:27:25.710') --difference between row 2 and row 143434,88888,datediff(ss,'2015-11-23 12:28:26.714','2015-11-23 12:28:27.710') --

difference between row 4 and row 3difference between row 6 and row 5...In the above query, I always want to find the difference in transaction date time between second and first row in a moving window.I have the unique id as rec_id to compare the next row with the previous row.

View 3 Replies View Related

Update Statistics Run With Users On?

Jan 24, 2002

Hello,

Can "update statistics tablename" be run while the server is in multi-user mode and users accessing the table to be updated?????

Thanks in advance...

View 1 Replies View Related

Automatic Update Statistics

Jan 11, 2001

On a SQL 7 sp 2 server, I have a database with about about 77,000 records, with automatic update statistics on inserting 1000 records took 43 minutes. With automatic update off, it took 23 minutes to insert the same 1000 records. On the same machine, I inserted 1000 records into 2 other databases with the same database structure and automatic update statistics on. On the second database, there are about 174000 records and it took 35 minutes to insert 1000 records. On the third database, there are about 93000 records and it took 19 minutes to insert 1000 records.

Why is the time so high on the first database?

What can be done to fix the problem?

Thank you.

View 8 Replies View Related

Why Do I Have To Update The Statistics So Frequently?

Oct 10, 2000

I have a query that retrieves a single record from searching on two tables.
The statement goes like this...
select sum(amount) from Table1 A union Table2 B on a.id = b.id
where date < ### and date > #### and account = ###

As people are running a particular report, this statement is executed time and
time again to pull up the numbers necesarry for the report. When the report gets slow, I can speed it up by updating the statistics. My concern is that I'm having to update the statistcs every hour; otherwise, the query becomes slow. I have noticed that users are inserting data while users are running the report on one of the tables listed above. I'm sure that's making it become more fragmented and ultimately slowing down the query.
Do you have any suggestion on how I can make the union of these two tables faster? Or is there anything I could do to speed the query besides creating clusted indexes?
Any help would be appreciated....thank you

View 1 Replies View Related

Update Statistics Vs. Reindex

Dec 14, 1999

I am maintaining a large table with millions of rows that has two non clustered indexes and data changing frequently, I need to keep the indexes fresh. Update Statistics runs much quicker than Reindex. What is the appropriate situation for each and why?
Thanks in advance.

View 1 Replies View Related

Reindexing Vs Update Statistics

Nov 22, 2004

Hi

We are upgrading from sql 7 to 2000.During the upgrade process do we have to do a reindexing of all tables or will update statistics take care of that.

Or do we have to do both?
What is the difference between reindexing and update statistics.

Thanks

Madhukar Gole

View 5 Replies View Related

Update Statistics On All Tables

Dec 8, 2006

I have recently defragged my SQL server using INDEXDEFRAG. Can somebody please tell me how to update the statistics on all the tables? Thanks in advance.

Below is the script that I executed to defrag all the tables in my database if anyone needs this.

/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL

-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 20.0

-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)

FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table
DROP TABLE #fraglist
GO

View 4 Replies View Related

UPDATE STATISTICS Questions

May 18, 2007

I am looking to run UPDATE STATISTICS for the first time, don't ask why it wasn't done prior please :(, on a set of large tables in our 346gb database whcih has been being populated with transactional data for the past 4 years. The tables contain 1.2, 35, 64, and 92 million rows. I have used the STAT_DATE function to determine that none of these tables have ever had update statistics run for them.

My question is how should I go about this process and what options should I be selecting when issuing the command? I assume that I must first run with the FULLSCAN paramater in order to initially generate statistics for the table then would assume that following this initial population I could run without any paramaters nightly against the tables in the database to keep statistics up to date. Any guideance you all could provide to a newb would be greatly appreciated.

Thanks! Scott Clark

View 8 Replies View Related

Update Statistics On Table

Apr 15, 2008

Hi Gurus,

I need you update statistics on table .

Is there any command or stored procedure for this.

Thanks,
ServerTeam

View 2 Replies View Related