Viewing 15 posts - 5,101 through 5,115 (of 7,506 total)
Per current performance data it has executed the delete 787x and has take 45s CPU, 52s duration, and 21093244 Logical Reads.
Yep, that's what occurs with delete.
A truncate is only...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
March 11, 2008 at 7:11 am
Truncate is the fastest way ! (minimal logged)
Delete is a logged operation and will cause the most locking overhead as well as logging overhead (db-logfile)!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
March 11, 2008 at 6:14 am
For the non catalog files you can use:
DBCC SHRINKFILE ( file_name , EMPTYFILE )
and later on
alter database xyz REMOVE FILE logical_file_name
Read BOL for more...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
March 11, 2008 at 1:36 am
Oops, I missed the reconfigure statement.
That's why not all config options show up.
exec sp_configure 'show advanced options' , 1
reconfigure
exec sp_configure -- information (extended info activated please)
Can you post again...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
March 11, 2008 at 1:21 am
well ... a function (datediff) can be avoided just by adding
one variable, calculating that and then have a straight comparisson
where clause.
This way chances of index usage will be...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
March 11, 2008 at 1:02 am
Maybe.
If you add columns not null with an actual default value, rows will be rewritten, so in that case, yes , then the old columns will be gone.
Otherwise no because...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
March 10, 2008 at 2:06 pm
if you just want to kick everybody out user alter database
alter database xyz set single_user with rollback immediate;
restore database xyz
...
keep in mind NOT to place a "go" in between...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
March 10, 2008 at 2:04 pm
can you provide some more specific info ?
OS ? (+ sp)
SQL version + sp
exec sp_configure 'show advanced options' , 1
exec sp_configure -- information (extended info activated please)
This mainly to...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
March 10, 2008 at 8:49 am
did you grant all sqlservers service accounts the needed folder authorities for the new location ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
March 10, 2008 at 8:37 am
Looking good.
Indeed the delete statement does not need the "*" :blush:
Think DRP: start with a full db backup for this operation !
Since this proc only has this single while loop...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
March 10, 2008 at 8:27 am
DBA (3/10/2008)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
March 10, 2008 at 7:52 am
- that may be a good option if 2M is the majority or rows in your table.
- If not, you may want to use a while loop to just delete...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
March 10, 2008 at 6:59 am
I'm sorry I've forgotten to mention just to put my code
right before the housekeeping section (last line) of Jeffs.
:blush:
-- put my code overhere
--===== Housekeeping
DROP TABLE #Tally
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
March 10, 2008 at 6:44 am
you could start with the using the new sql2005 except join
then unpivot the resultset
and then check for the differences.
remarks: datatypes will get converted to varchar(max)
(I did put max...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
March 10, 2008 at 3:31 am
Sergiy (3/9/2008)
SELECT @SaleAmount = @SaleAmount * (1-Amount)FROM dbo.Temp_Discount
ORDER BY OrderID
There is a very nice must read article bij Jeff Moden regarding this construct.
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
March 10, 2008 at 2:08 am
Viewing 15 posts - 5,101 through 5,115 (of 7,506 total)