Viewing 15 posts - 5,896 through 5,910 (of 7,619 total)
For best performance overall, I strongly suspect you need to change the clustered indexes on the tables. We would have to look at least at missing index and index...
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".
August 6, 2014 at 5:09 pm
LutzM (8/6/2014)
If the table contains a rather large number of columns (e.g. 50 or more) you might consider "vertical partitioning",...
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".
August 6, 2014 at 5:00 pm
Basically, I want to wait for the job to complete and then send either a success or failure email based on the outcome of the job.
Again, I urge you to...
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".
August 5, 2014 at 9:45 am
For that, you could create a proc on the remote server. You execute the proc remotely, and the proc can issue the start job, and then monitor for job...
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".
August 4, 2014 at 6:34 am
8 hours seems extraordinarily extreme for 172M rows. It seems like there must be more to the story.
Are you changing the clustered index on the table? Does the...
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".
August 3, 2014 at 11:40 am
I'd also create the best indexes while re-working the code.
#Events indexes:
1) Unique Clustered on ( ProcessDate, L4Ident ) --in that order
2) Nonclustered on ( L4Ident )
3) NNonclustered on ( LinkRL4,...
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".
August 1, 2014 at 11:25 am
dkriplani97 (8/1/2014)
UPDATE TableA
SET Value = isnull((select top 1 Value from #tmpcopylibrary
where dataid = tempquerydataid), 0).
however this is not working
UPDATE TableA
SET Value = isnull((select top 1...
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".
August 1, 2014 at 11:11 am
OPENQUERY does not allow variables, only literal/static queries.
Try code below instead.
--temporarily add "TOP (100)" to the query for testing purposes
SET @tsql = STUFF(@tsql, CHARINDEX('SELECT', @tsql) + 6, 0, ' TOP...
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".
August 1, 2014 at 10:59 am
Eirikur Eiriksson (8/1/2014)
ScottPletcher (8/1/2014)
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".
August 1, 2014 at 8:57 am
If the table may or may not exist, you'll have to make references to that table only in dynamic SQL. Otherwise, if the code runs and the table 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".
August 1, 2014 at 8:03 am
Agreed, nice code.
But if it's a birthday, wouldn't it be reasonable to use the current date + 1 as the cutoff, since there won't be future birthdates but someone could...
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".
July 31, 2014 at 5:38 pm
SQL Server provides a stored proc, "'sp_spaceused", to return space info. But annoyingly and frustratingly, it returns two separate result sets.
For my use, I cloned their proc, adjusting it...
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".
July 31, 2014 at 5:11 pm
Books should be more valuable than classes. Look for titles by Kalen Delaney particularly. For general SQL skills, look for Itzik Ben-Gan.
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".
July 31, 2014 at 3:57 pm
Shrinking the log file has absolutely no affect on the data indexes and their fragmentation. You would only need to check on index rebuilds if you shrink the data...
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".
July 31, 2014 at 8:03 am
I suspect it's because SQL's internal processing does "ON" clauses before WHERE clauses.
Therefore, I think they figure the ON clause might eliminate rows sooner than a WHERE clause, and avoid...
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".
July 30, 2014 at 3:55 pm
Viewing 15 posts - 5,896 through 5,910 (of 7,619 total)