interview question.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

interview question....

Author

Topic

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2009-06-26 : 11:54:42

1.What would you do to find the reason and to resolve a performance issue with a report that usually takes ½ hour and now is not finishing after 3 hours?

2.How do you fix corrupted pages?
Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 13:01:54

1a. Check execution plan
1b. See if there is a need to rebuild index(es).
1c. See if there is 6 times as much data than last execution

E 12°55'05.63"
N 56°04'39.26"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-06-26 : 13:22:01

2.
run dbcc checkdb. depending on its output you might have simple NCI corruption which is fixed by rebuilding your NCI's. then there are other more destructive ways of corruption which are fixed by restoring from backups. this of course needs proper backup/restore strategy. the last resort is doing a DBCC CHECKDB WITH ALLOW_DATA_LOSS which will do exactly what the name says. it will destroy any data it needs to bring the database into a consistent form.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-06-29 : 07:02:34

Run the execution plan (If server is production use profiler to capture the xml execution plan)
Find how the Query Optimizer uses indexes
Update the statistics
Rebuild index

by
raj...:)

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-06-29 : 07:06:58

DBCC CHECKDB ('Database Name',REPAIR_ALLOW_DATA_LOSS)

Note
----
Database needs to be in single user mode.

by
raj...:)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-29 : 07:36:20

quote:
Originally posted by rajdaksha

DBCC CHECKDB ('Database Name',REPAIR_ALLOW_DATA_LOSS)

Note
----
Database needs to be in single user mode.

by
raj...:)

Never do this except as a LAST RESORT.

When Spirit1 said that

quote:

the last resort is doing a DBCC CHECKDB WITH ALLOW_DATA_LOSS which will do exactly what the name says. it will destroy any data it needs to bring the database into a consistent form.

He *really* meant it. Check out Paul Randal's blogs: http://www.sqlskills.com/blogs/paul/ (he wrote the utility). There's a section on ALLOW_DATA_LOSS. It doesn't try and recover data it might be able to it just gets the database into a usable state.

Edit: Only one l in Randal -- sorry paul

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-06-29 : 07:55:21

Regarding corruption...
[url]http://www.sqlservercentral.com/articles/65804/[/url]

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources