Hello
I'm in the proces of a major revision of the maintenance plans for the SQL servers in our company, and in connection with that I would like to hear how other people are doing this.
Here's a quick rundown of the plan:
Critical DBs will be backed up on tape (daily incremental, weekly full, w. Veritas Backup Exec 9.0). Also, there will be full daily disk backups for easy quick recovery. These will be done locally, as I have bad experiences trying to backup across a network share.
DBs of medium importance will be backed up fully every day on disk. These BAK-files will then be backed up on tape, if I find it necessary.
Although I rarely restore from the tapes, I think they're nice to have in case the office burns down or who knows what.
The maintenance plans will be split into 3:
1. System DBs maint. plan
2. Critical importance DBs maint. plan
3. Medium importance DBs maint. plan
The more I think about it, the more I think I might just classify all production DBs as critical and all test DBs as medium. Maybe that would make more sense.
For all disk backups, optimization and integrity checks (and backup) will be done daily. For DBs of critical importance (eg. production DBs) Transaction log back will be done as well. What's a good schedule for this? Once every 3 hours or so? Every hour? How much burden does this operation put on the server?
I guess that's about it so far. If anyone has any suggestions or comments, I would be very pleased to hear them.
MNJFrequency of trx. log backups depends on the level of activity of action queries and recoverability requirements. In one of our databases here we're doing 15-minute trx. log dumps and the resulting file varies from 800MB to 2.5GB in size. Another database barely creates a 100K logs but we're doing dumps every 30 minutes for its point-in-time recoverability requirements. It all depends.
As per your breakdown, it looks good. But as our disaster recovery excersises shown, - it's beneficial to have your system databases backed up last. Here we're using SQLMAINT utility to run our maintenance plans (SQLMAINT -PlanName <app_db_maint_plan>). This way it's easier to sequence the steps to your likes. Also, do make sure you log all outputs, in case something goes south :)|||[i]As per your breakdown, it looks good. But as our disaster recovery excersises shown, - it's beneficial to have your system databases backed up last. Here we're using SQLMAINT utility to run our maintenance plans (SQLMAINT -PlanName <app_db_maint_plan>). This way it's easier to sequence the steps to your likes. Also, do make sure you log all outputs, in case something goes south :)
Why is it beneficial to backup the system DBs last? Since I keep system and user DBs separated into different maint. plans, I guess I can just schedule the system maint. plan to occur 15 min. after the user main. plan?
MNJ|||For one, if MSDB is backed up last, - it will contain the latest backup information of all other databases, as well as itself. This information is available when looking at the General tab of database Properties window.
As per scheduling, - as I said earlier, I have execution of all maintenance plans in one batch with SQLMAINT. If you're using Scheduled Tasks, then you can add a step with SQLMAINT -PlanName <sys_db_maint_plan> after your application databases.|||Good point with system DBs, I will take that into consideration. I suppose if I just make sure to schedule them a bit apart, it should work out ok.
MNJ
Showing posts with label plan. Show all posts
Showing posts with label plan. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Receive java.io.InputStream frm PL/SQL
I am wondering how to recieve a stream of data from pl/sql to a java-class stored in the database.
Right now my plan is to pass a LOB to java and treat it as an InputStream, but I am not sure if it is possible.
Is it anyone here who knows?
-ajI found out myself!!!!!
If anyone has the same problem:
First create a temporary CLOB in PL/SQL and fill it with the data you need to stream.
This CLOB can be passed directly to the java-class via a java-wrapper. Use the datatype java.sql.Clob
In your java-method you receive an InputStream from the CLOB by calling the method getAsciiStream() on the Clob-object
Right now my plan is to pass a LOB to java and treat it as an InputStream, but I am not sure if it is possible.
Is it anyone here who knows?
-ajI found out myself!!!!!
If anyone has the same problem:
First create a temporary CLOB in PL/SQL and fill it with the data you need to stream.
This CLOB can be passed directly to the java-class via a java-wrapper. Use the datatype java.sql.Clob
In your java-method you receive an InputStream from the CLOB by calling the method getAsciiStream() on the Clob-object
Friday, March 23, 2012
Rebuilding Indexes
Hello all,
I need to diagnose a problem, this Sunday a regular Database Maintenance plan which is supposed to rebuild indexes took exactly 6 hours and 32 minutes. Now thats a hell lot of time and during all that process users were denied access to those tables. This is a production server. I want to know what caused that plan to run for so long and how can I avoid this to happen again plus if it ever happens again how can I make sure that atleast it doesnt lock tables. I know DBCC INDEXDEFRAG doesnt lock tables but how can I make Database Maintenance plan to run DBCC INDEXDEFRAG instead of DBCC DBREINDEX but more importantly why it took 6 hours.
Thanks allThere a lots of reasons that the process could have lasted so long. Do you have any evidence to indicate that this length of time is longer than normal (ie, what did it take last weekend and the weekend before that)?
Some things to possible look for:
1. A transaction that hung
2. Disk space (for both the Data and TLog files)
3. Disk space (for the TempDB)
4. Other jobs or indicators that ran over-long during this period
Also, look at your SQL Error Log for the time period and determine if there are any errors or warnings related to the problem. Look at you your System and Applcation Error Logs for indications that are related to the problem.
As for DBREINDEX vs. INDEXDEFRAG, I might suggest saving the former for quarterly or otherwise routine maintenance periods when you are guaranteed a "clean" box to work on (ie, no user connections, no activity). You can script off a T-SQL that will do an INDEXDEFRAG on specified tables (or all of them if you want).
Regards,
hmscott|||more data? that's usually the number one reason ;)
I need to diagnose a problem, this Sunday a regular Database Maintenance plan which is supposed to rebuild indexes took exactly 6 hours and 32 minutes. Now thats a hell lot of time and during all that process users were denied access to those tables. This is a production server. I want to know what caused that plan to run for so long and how can I avoid this to happen again plus if it ever happens again how can I make sure that atleast it doesnt lock tables. I know DBCC INDEXDEFRAG doesnt lock tables but how can I make Database Maintenance plan to run DBCC INDEXDEFRAG instead of DBCC DBREINDEX but more importantly why it took 6 hours.
Thanks allThere a lots of reasons that the process could have lasted so long. Do you have any evidence to indicate that this length of time is longer than normal (ie, what did it take last weekend and the weekend before that)?
Some things to possible look for:
1. A transaction that hung
2. Disk space (for both the Data and TLog files)
3. Disk space (for the TempDB)
4. Other jobs or indicators that ran over-long during this period
Also, look at your SQL Error Log for the time period and determine if there are any errors or warnings related to the problem. Look at you your System and Applcation Error Logs for indications that are related to the problem.
As for DBREINDEX vs. INDEXDEFRAG, I might suggest saving the former for quarterly or otherwise routine maintenance periods when you are guaranteed a "clean" box to work on (ie, no user connections, no activity). You can script off a T-SQL that will do an INDEXDEFRAG on specified tables (or all of them if you want).
Regards,
hmscott|||more data? that's usually the number one reason ;)
Tuesday, March 20, 2012
Rebuild Index Task and Sytem databases
The rebuild index task in a maintenance plan allows you to choose
system databases. I noticed that some of the system database tables
do have indexes. Should you run this task on system databases
within the maintenance plan. Is it necessary and will it do anything.
Also will it cause any issue with these databases. What about reorganize
index task or update statistics will this cause any issues and should it be run.
Lastly can you shrink a system database. For instance should you run the
shrink database task. Any help would be greatly appreciated thank you.i have never even thought about doing this. are you experiencing performance problems related to the system databases? do you have index fragmentation in the system databases? if not leave well enough alone. if it ain't broke...
if you do this, try it on your local sql install or a dev machine first and do a sanity check.|||This was an inherited system and there was a maintenance plan that did
all of these things. I was a little bit skeptical myself. I was just wondering
if anybody has tried some of these things and what it would do.
system databases. I noticed that some of the system database tables
do have indexes. Should you run this task on system databases
within the maintenance plan. Is it necessary and will it do anything.
Also will it cause any issue with these databases. What about reorganize
index task or update statistics will this cause any issues and should it be run.
Lastly can you shrink a system database. For instance should you run the
shrink database task. Any help would be greatly appreciated thank you.i have never even thought about doing this. are you experiencing performance problems related to the system databases? do you have index fragmentation in the system databases? if not leave well enough alone. if it ain't broke...
if you do this, try it on your local sql install or a dev machine first and do a sanity check.|||This was an inherited system and there was a maintenance plan that did
all of these things. I was a little bit skeptical myself. I was just wondering
if anybody has tried some of these things and what it would do.
Rebuild Index Failing
The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
failing.
The text file report is of very little help...
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
Report was generated on "SQLSERVER".
Maintenance Plan: Weekly All DB Maintenance Plan
Duration: 00:00:00
Status: Succeeded.
Details:
The job history doesn't tell much either...
Date3/15/2007 8:00:00 PM
LogJob History (Weekly All DB Maintenance Plan)
Step ID0
ServerSQLSERVER
Job NameWeekly All DB Maintenance Plan
Step Name(Job outcome)
Duration04:25:27
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
The job failed. The Job was invoked by Schedule 99 (Weekly All DB
Maintenance Plan). The last step to run was step 1 (Subplan).
These symptoms are worse on (but not limited to) databases migrated from SQL
2000.
I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
Index to run. But eventually Rebuild Index will fail requiring UPDATEUSAGE
be run again.
Is there any way to get a more verbose (meaningful) task report?
What is causing this?
Is there any harm in running UPDATEUSAGE on a regular basis?
Thanks in advance,
John
John,
SP2 will give you a lot more output but there are known problems with maint
plans and checkdb. See:-
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/03/08/be-aware-sp2-maint-plans-and-checkdb.aspx
Chris
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John
|||Its well known that index usage counts could get out of whack on SQL 2000
but the algorithm was changed for 2005 suh that they never should. I'd
really like to know more about this - can you send me email with the error
messages you're getting from the rebuilds? (Go to the blog site below and
send email through there).
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John
|||Hi Paul,
Thank you for taking an interest in my little dilemma.
Forgive me but I don't see how to send an email from your blog. Can you
please expand on how to do it?
Thanks,
John
"Paul S. Randal [MS]" wrote:
> Its well known that index usage counts could get out of whack on SQL 2000
> but the algorithm was changed for 2005 suh that they never should. I'd
> really like to know more about this - can you send me email with the error
> messages you're getting from the rebuilds? (Go to the blog site below and
> send email through there).
> Thanks
> --
> Paul Randal
> Principal Lead Program Manager
> Microsoft SQL Server Core Storage Engine,
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>
> "John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
> news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
>
>
failing.
The text file report is of very little help...
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
Report was generated on "SQLSERVER".
Maintenance Plan: Weekly All DB Maintenance Plan
Duration: 00:00:00
Status: Succeeded.
Details:
The job history doesn't tell much either...
Date3/15/2007 8:00:00 PM
LogJob History (Weekly All DB Maintenance Plan)
Step ID0
ServerSQLSERVER
Job NameWeekly All DB Maintenance Plan
Step Name(Job outcome)
Duration04:25:27
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
The job failed. The Job was invoked by Schedule 99 (Weekly All DB
Maintenance Plan). The last step to run was step 1 (Subplan).
These symptoms are worse on (but not limited to) databases migrated from SQL
2000.
I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
Index to run. But eventually Rebuild Index will fail requiring UPDATEUSAGE
be run again.
Is there any way to get a more verbose (meaningful) task report?
What is causing this?
Is there any harm in running UPDATEUSAGE on a regular basis?
Thanks in advance,
John
John,
SP2 will give you a lot more output but there are known problems with maint
plans and checkdb. See:-
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/03/08/be-aware-sp2-maint-plans-and-checkdb.aspx
Chris
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John
|||Its well known that index usage counts could get out of whack on SQL 2000
but the algorithm was changed for 2005 suh that they never should. I'd
really like to know more about this - can you send me email with the error
messages you're getting from the rebuilds? (Go to the blog site below and
send email through there).
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John
|||Hi Paul,
Thank you for taking an interest in my little dilemma.
Forgive me but I don't see how to send an email from your blog. Can you
please expand on how to do it?
Thanks,
John
"Paul S. Randal [MS]" wrote:
> Its well known that index usage counts could get out of whack on SQL 2000
> but the algorithm was changed for 2005 suh that they never should. I'd
> really like to know more about this - can you send me email with the error
> messages you're getting from the rebuilds? (Go to the blog site below and
> send email through there).
> Thanks
> --
> Paul Randal
> Principal Lead Program Manager
> Microsoft SQL Server Core Storage Engine,
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>
> "John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
> news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
>
>
Rebuild Index Failing
The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
failing.
The text file report is of very little help...
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
Report was generated on "SQLSERVER".
Maintenance Plan: Weekly All DB Maintenance Plan
Duration: 00:00:00
Status: Succeeded.
Details:
The job history doesn't tell much either...
Date 3/15/2007 8:00:00 PM
Log Job History (Weekly All DB Maintenance Plan)
Step ID 0
Server SQLSERVER
Job Name Weekly All DB Maintenance Plan
Step Name (Job outcome)
Duration 04:25:27
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. The Job was invoked by Schedule 99 (Weekly All DB
Maintenance Plan). The last step to run was step 1 (Subplan).
These symptoms are worse on (but not limited to) databases migrated from SQL
2000.
I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
Index to run. But eventually Rebuild Index will fail requiring UPDATEUSAGE
be run again.
Is there any way to get a more verbose (meaningful) task report?
What is causing this?
Is there any harm in running UPDATEUSAGE on a regular basis?
Thanks in advance,
JohnJohn,
SP2 will give you a lot more output but there are known problems with maint
plans and checkdb. See:-
http://sqlblog.com/blogs/tibor_kara...nd-checkdb.aspx
Chris
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John|||Its well known that index usage counts could get out of whack on SQL 2000
but the algorithm was changed for 2005 suh that they never should. I'd
really like to know more about this - can you send me email with the error
messages you're getting from the rebuilds? (Go to the blog site below and
send email through there).
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John|||Hi Paul,
Thank you for taking an interest in my little dilemma.
Forgive me but I don't see how to send an email from your blog. Can you
please expand on how to do it?
Thanks,
John
"Paul S. Randal [MS]" wrote:
> Its well known that index usage counts could get out of whack on SQL 2000
> but the algorithm was changed for 2005 suh that they never should. I'd
> really like to know more about this - can you send me email with the error
> messages you're getting from the rebuilds? (Go to the blog site below and
> send email through there).
> Thanks
> --
> Paul Randal
> Principal Lead Program Manager
> Microsoft SQL Server Core Storage Engine,
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
>
> "John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
> news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
>
>
failing.
The text file report is of very little help...
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
Report was generated on "SQLSERVER".
Maintenance Plan: Weekly All DB Maintenance Plan
Duration: 00:00:00
Status: Succeeded.
Details:
The job history doesn't tell much either...
Date 3/15/2007 8:00:00 PM
Log Job History (Weekly All DB Maintenance Plan)
Step ID 0
Server SQLSERVER
Job Name Weekly All DB Maintenance Plan
Step Name (Job outcome)
Duration 04:25:27
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. The Job was invoked by Schedule 99 (Weekly All DB
Maintenance Plan). The last step to run was step 1 (Subplan).
These symptoms are worse on (but not limited to) databases migrated from SQL
2000.
I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
Index to run. But eventually Rebuild Index will fail requiring UPDATEUSAGE
be run again.
Is there any way to get a more verbose (meaningful) task report?
What is causing this?
Is there any harm in running UPDATEUSAGE on a regular basis?
Thanks in advance,
JohnJohn,
SP2 will give you a lot more output but there are known problems with maint
plans and checkdb. See:-
http://sqlblog.com/blogs/tibor_kara...nd-checkdb.aspx
Chris
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John|||Its well known that index usage counts could get out of whack on SQL 2000
but the algorithm was changed for 2005 suh that they never should. I'd
really like to know more about this - can you send me email with the error
messages you're getting from the rebuilds? (Go to the blog site below and
send email through there).
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John|||Hi Paul,
Thank you for taking an interest in my little dilemma.
Forgive me but I don't see how to send an email from your blog. Can you
please expand on how to do it?
Thanks,
John
"Paul S. Randal [MS]" wrote:
> Its well known that index usage counts could get out of whack on SQL 2000
> but the algorithm was changed for 2005 suh that they never should. I'd
> really like to know more about this - can you send me email with the error
> messages you're getting from the rebuilds? (Go to the blog site below and
> send email through there).
> Thanks
> --
> Paul Randal
> Principal Lead Program Manager
> Microsoft SQL Server Core Storage Engine,
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
>
> "John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
> news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
>
>
Rebuild Index Failing
The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
failing.
The text file report is of very little help...
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
Report was generated on "SQLSERVER".
Maintenance Plan: Weekly All DB Maintenance Plan
Duration: 00:00:00
Status: Succeeded.
Details:
The job history doesn't tell much either...
Date 3/15/2007 8:00:00 PM
Log Job History (Weekly All DB Maintenance Plan)
Step ID 0
Server SQLSERVER
Job Name Weekly All DB Maintenance Plan
Step Name (Job outcome)
Duration 04:25:27
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. The Job was invoked by Schedule 99 (Weekly All DB
Maintenance Plan). The last step to run was step 1 (Subplan).
These symptoms are worse on (but not limited to) databases migrated from SQL
2000.
I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
Index to run. But eventually Rebuild Index will fail requiring UPDATEUSAGE
be run again.
Is there any way to get a more verbose (meaningful) task report?
What is causing this?
Is there any harm in running UPDATEUSAGE on a regular basis?
Thanks in advance,
JohnJohn,
SP2 will give you a lot more output but there are known problems with maint
plans and checkdb. See:-
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/03/08/be-aware-sp2-maint-plans-and-checkdb.aspx
Chris
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John|||Its well known that index usage counts could get out of whack on SQL 2000
but the algorithm was changed for 2005 suh that they never should. I'd
really like to know more about this - can you send me email with the error
messages you're getting from the rebuilds? (Go to the blog site below and
send email through there).
Thanks
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John|||Hi Paul,
Thank you for taking an interest in my little dilemma.
Forgive me but I don't see how to send an email from your blog. Can you
please expand on how to do it?
Thanks,
John
"Paul S. Randal [MS]" wrote:
> Its well known that index usage counts could get out of whack on SQL 2000
> but the algorithm was changed for 2005 suh that they never should. I'd
> really like to know more about this - can you send me email with the error
> messages you're getting from the rebuilds? (Go to the blog site below and
> send email through there).
> Thanks
> --
> Paul Randal
> Principal Lead Program Manager
> Microsoft SQL Server Core Storage Engine,
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>
> "John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
> news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> > The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> > failing.
> >
> > The text file report is of very little help...
> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> > Report was generated on "SQLSERVER".
> > Maintenance Plan: Weekly All DB Maintenance Plan
> > Duration: 00:00:00
> > Status: Succeeded.
> > Details:
> >
> > The job history doesn't tell much either...
> >
> > Date 3/15/2007 8:00:00 PM
> > Log Job History (Weekly All DB Maintenance Plan)
> > Step ID 0
> > Server SQLSERVER
> > Job Name Weekly All DB Maintenance Plan
> > Step Name (Job outcome)
> > Duration 04:25:27
> > Sql Severity 0
> > Sql Message ID 0
> > Operator Emailed
> > Operator Net sent
> > Operator Paged
> > Retries Attempted 0
> >
> > Message
> > The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> > Maintenance Plan). The last step to run was step 1 (Subplan).
> >
> >
> >
> > These symptoms are worse on (but not limited to) databases migrated from
> > SQL
> > 2000.
> >
> > I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> > Index to run. But eventually Rebuild Index will fail requiring
> > UPDATEUSAGE
> > be run again.
> >
> > Is there any way to get a more verbose (meaningful) task report?
> >
> > What is causing this?
> >
> > Is there any harm in running UPDATEUSAGE on a regular basis?
> >
> > Thanks in advance,
> > John
>
>
failing.
The text file report is of very little help...
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
Report was generated on "SQLSERVER".
Maintenance Plan: Weekly All DB Maintenance Plan
Duration: 00:00:00
Status: Succeeded.
Details:
The job history doesn't tell much either...
Date 3/15/2007 8:00:00 PM
Log Job History (Weekly All DB Maintenance Plan)
Step ID 0
Server SQLSERVER
Job Name Weekly All DB Maintenance Plan
Step Name (Job outcome)
Duration 04:25:27
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. The Job was invoked by Schedule 99 (Weekly All DB
Maintenance Plan). The last step to run was step 1 (Subplan).
These symptoms are worse on (but not limited to) databases migrated from SQL
2000.
I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
Index to run. But eventually Rebuild Index will fail requiring UPDATEUSAGE
be run again.
Is there any way to get a more verbose (meaningful) task report?
What is causing this?
Is there any harm in running UPDATEUSAGE on a regular basis?
Thanks in advance,
JohnJohn,
SP2 will give you a lot more output but there are known problems with maint
plans and checkdb. See:-
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/03/08/be-aware-sp2-maint-plans-and-checkdb.aspx
Chris
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John|||Its well known that index usage counts could get out of whack on SQL 2000
but the algorithm was changed for 2005 suh that they never should. I'd
really like to know more about this - can you send me email with the error
messages you're getting from the rebuilds? (Go to the blog site below and
send email through there).
Thanks
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John|||Hi Paul,
Thank you for taking an interest in my little dilemma.
Forgive me but I don't see how to send an email from your blog. Can you
please expand on how to do it?
Thanks,
John
"Paul S. Randal [MS]" wrote:
> Its well known that index usage counts could get out of whack on SQL 2000
> but the algorithm was changed for 2005 suh that they never should. I'd
> really like to know more about this - can you send me email with the error
> messages you're getting from the rebuilds? (Go to the blog site below and
> send email through there).
> Thanks
> --
> Paul Randal
> Principal Lead Program Manager
> Microsoft SQL Server Core Storage Engine,
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>
> "John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
> news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> > The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> > failing.
> >
> > The text file report is of very little help...
> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> > Report was generated on "SQLSERVER".
> > Maintenance Plan: Weekly All DB Maintenance Plan
> > Duration: 00:00:00
> > Status: Succeeded.
> > Details:
> >
> > The job history doesn't tell much either...
> >
> > Date 3/15/2007 8:00:00 PM
> > Log Job History (Weekly All DB Maintenance Plan)
> > Step ID 0
> > Server SQLSERVER
> > Job Name Weekly All DB Maintenance Plan
> > Step Name (Job outcome)
> > Duration 04:25:27
> > Sql Severity 0
> > Sql Message ID 0
> > Operator Emailed
> > Operator Net sent
> > Operator Paged
> > Retries Attempted 0
> >
> > Message
> > The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> > Maintenance Plan). The last step to run was step 1 (Subplan).
> >
> >
> >
> > These symptoms are worse on (but not limited to) databases migrated from
> > SQL
> > 2000.
> >
> > I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> > Index to run. But eventually Rebuild Index will fail requiring
> > UPDATEUSAGE
> > be run again.
> >
> > Is there any way to get a more verbose (meaningful) task report?
> >
> > What is causing this?
> >
> > Is there any harm in running UPDATEUSAGE on a regular basis?
> >
> > Thanks in advance,
> > John
>
>
Rebuild index failed
I scheduled a maint plan to rebuild index. All table's indexes are successfully built except for one table. The error message I got:
Failed:(-1073548784) Executing the query "ALTER INDEX [Index_name] ON [dbo].[table] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON )
" failed with the following error: "Online index operations can only be performed in Enterprise edition of SQL Server.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Can someone tell me what the problem is and how to fix it? Thanks.
I am assuming you are using the Enterprise Edition of SQL 2005?|||We are using Enterprise Edition. All other tables can be reindexed. This is the only table has problem. And this is not a big table. The indexed column is nvarchar(50).
Any ideas?
Wednesday, March 7, 2012
Realtive query cost in the execution plan
Hello everyone!
I have a general question about measuring query cost while tuning
queries. If I put both queries (the old one and the modified one) into
the same batch, can I then use the query cost (relative to the batch)
percentage number as a reliable way of measuring whether the performance
has improved?
I would also appreciate any links to the good articles on this matter.
Thank you,
Igor
*** Sent via Developersdex http://www.examnotes.net ***that's a good way to start - if it really unbalanced (e.g., version 1 is
90% of the total cost), then i've typically satisfied as to which is best.
however, i've found the most accurate way to measure it is to get the
actual timings over at least 10 runs. if it's a really long query, this
may not be totally feasible
e.g.
declare @.x datetime
set @.x = getdate()
-- run version 1
select datediff(ms, @.x, getdate()) as Version1_timing
set @.x = getdate()
-- run version 2
select datediff(ms, @.x, getdate()) as Version2_timing
mEmENT0m0RI wrote:
> Hello everyone!
> I have a general question about measuring query cost while tuning
> queries. If I put both queries (the old one and the modified one) into
> the same batch, can I then use the query cost (relative to the batch)
> percentage number as a reliable way of measuring whether the performance
> has improved?
> I would also appreciate any links to the good articles on this matter.
> Thank you,
> Igor
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Trey,
Thanks for your reply.
How about measuring the absolute query cost? Let's say I've added some
indexes and want to see the performance difference?
*** Sent via Developersdex http://www.examnotes.net ***|||by "absolute" do you mean what it actually uses rather than the estimate?
if so, run the queries again, but with the Show Execution Plan option
on. (CTRL-K is the shortcut).
this will show the actual query plan used - btw: you'll have to use this
option rather than the estimated query plan if temp tables are involved.
mEmENT0m0RI wrote:
> Trey,
> Thanks for your reply.
> How about measuring the absolute query cost? Let's say I've added some
> indexes and want to see the performance difference?
>
> *** Sent via Developersdex http://www.examnotes.net ***
I have a general question about measuring query cost while tuning
queries. If I put both queries (the old one and the modified one) into
the same batch, can I then use the query cost (relative to the batch)
percentage number as a reliable way of measuring whether the performance
has improved?
I would also appreciate any links to the good articles on this matter.
Thank you,
Igor
*** Sent via Developersdex http://www.examnotes.net ***that's a good way to start - if it really unbalanced (e.g., version 1 is
90% of the total cost), then i've typically satisfied as to which is best.
however, i've found the most accurate way to measure it is to get the
actual timings over at least 10 runs. if it's a really long query, this
may not be totally feasible
e.g.
declare @.x datetime
set @.x = getdate()
-- run version 1
select datediff(ms, @.x, getdate()) as Version1_timing
set @.x = getdate()
-- run version 2
select datediff(ms, @.x, getdate()) as Version2_timing
mEmENT0m0RI wrote:
> Hello everyone!
> I have a general question about measuring query cost while tuning
> queries. If I put both queries (the old one and the modified one) into
> the same batch, can I then use the query cost (relative to the batch)
> percentage number as a reliable way of measuring whether the performance
> has improved?
> I would also appreciate any links to the good articles on this matter.
> Thank you,
> Igor
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Trey,
Thanks for your reply.
How about measuring the absolute query cost? Let's say I've added some
indexes and want to see the performance difference?
*** Sent via Developersdex http://www.examnotes.net ***|||by "absolute" do you mean what it actually uses rather than the estimate?
if so, run the queries again, but with the Show Execution Plan option
on. (CTRL-K is the shortcut).
this will show the actual query plan used - btw: you'll have to use this
option rather than the estimated query plan if temp tables are involved.
mEmENT0m0RI wrote:
> Trey,
> Thanks for your reply.
> How about measuring the absolute query cost? Let's say I've added some
> indexes and want to see the performance difference?
>
> *** Sent via Developersdex http://www.examnotes.net ***
Monday, February 20, 2012
Ready Tool for SQL Ce Compact Edition
We have a plan to use Microsoft SQL Server 2005 Compact Edition RC1 as a backend. Our Front-end is VS .NET (VB.NET).
We want to know how to edit the Compact Edition database (.SDF). Is there any editor is available right now in the market? or microsoft provided any editor for this.
OR Can i use microsoft 2005 sql server standard edition for this.
Thanks
You can find two desktop editors in my company's website. The first is based on .NET 1.1 and is now showing its age. If you want to take a look at the second, please be advised that although it has much more features, it is still a draft (but works). Comments and suggestions are welcome.|||You can edit the .sdf right in vs2005 or SQL 2005 Management|||I use SQL Server 2005 Management Studio to edit it.
But be weary, the functionality is very limited. For example, you can't add foreign key constraints, or editing an existing table, in many cases, requires you to rebuild the table... but there isn't a feature to export your table to a script.
I would highly suggest scripting the table instead of using the GUI.
|||Hi,
After a great struggle we have comeup with our own tool designed for managing the SQL compact edition database.
Here the url http://www.satwhiz.com/datapump/info.html . You can download the beta tool.
Please post your comments to gopalan@.sofist.com
Thanks
Gops
Sofist India
Subscribe to:
Posts (Atom)