Viewing 15 posts - 6,001 through 6,015 (of 7,613 total)
Igor Micev (6/20/2014)
ScottPletcher (6/20/2014)
Is snapshot isolation in use?
The fact that his delete had been waiting to commit tells you that it's not a snapshot isolation level in use.
No, it does...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 20, 2014 at 1:31 pm
Taking a higher-level view, what's most important for performance of very large tables is making sure the clustered index is based on the best column(s). That can often eliminate...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 20, 2014 at 1:27 pm
Are you just trying to determine the relative Saturday# within the month? If so, there are methods with vastly less difficulty and overhead.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 20, 2014 at 1:05 pm
Does the read query use "WITH (NOLOCK)"?
Is snapshot isolation in use?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 20, 2014 at 12:57 pm
Minor correction:
t.TransactionDate >= 'Apr 2014' AND
t.TransactionDate < DATEADD(mm,1,'Apr 2014') --"<" rather than "<="
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 18, 2014 at 2:20 pm
If you basically just want to copy/clone one table but without the identity, why not just "cancel" the identity as part of the SELECT ... INTO the new table? ...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 18, 2014 at 2:17 pm
If I run a bunch of DELETE statements not wrapped in a BEGIN TRAN, COMMIT TRAN, will my deletes commit after each statement?
Yes, because SQL DELETE | INSERT | UPDATE...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 18, 2014 at 2:14 pm
seequill (6/16/2014)
They also have indexes on the keys that I am linking them with too.
Verify that those indexes are actually being used; an estimated query plan is enough for
You...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 16, 2014 at 4:57 pm
If you are using a native SQL account, you can check in:
sys.server_principals
There is no date specifically for password changes, but the modify_date does get reset when the password is changed.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 16, 2014 at 1:13 pm
Jeff Moden (6/13/2014)
Jake Shelton (6/12/2014)
I'm looking for the most time-efficient way to backup db's to a local drive and then move the .bak files across to a new server,...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 13, 2014 at 10:28 pm
I deferred converting the date to character until the very end to avoid having to convert every date and to avoid grouping on a char/varchar.
SELECT right('0' + cast(month(ClosingMonth) as...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 12, 2014 at 4:40 pm
You also need to get rid of all the ISNULL()s in the WHERE clause. You should follow this simple rule:
Never use ISNULL() in a WHERE clause.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 12, 2014 at 4:29 pm
The quick way would be switch the "=" and "<" in the current code 😉 :
delete table
from table t join
(select col1,col2,col3,col4, from table
where convert(varchar,insertdate,112) = convert(varchar,getdate(),112)
) y
on t.col1...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 12, 2014 at 2:39 pm
I also suggest considering average wait time as opposed to only using total wait time:
select
case when waiting_tasks_count = 0 then 0 else
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 12, 2014 at 2:37 pm
It deletes only today's date, but only if there are matching records before today.
Apparently, based on the code, only the earliest matching row, across the 4 columns compared, is needed...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 12, 2014 at 1:55 pm
Viewing 15 posts - 6,001 through 6,015 (of 7,613 total)