Viewing 15 posts - 5,956 through 5,970 (of 7,613 total)
Here's a few thoughts:
If T270.C18133 might often be less than 19 bytes, add this to the WHERE clause:
WHERE
LEN(T270.C18133) >= 19
Since you don't have an ending %,...
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 15, 2014 at 12:57 pm
Interesting, but not definitive.
Would you please run these commands on that database and post the results? That will show what indexes SQL "thinks" are missing, and how existing indexes...
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 15, 2014 at 10:42 am
It will cover that specific process as it exists now, yes, but you're doubling one table's size. And you'll have to periodically go back and re-do the "covering" 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".
July 15, 2014 at 10:19 am
D'OH, sorry, I left off one all-important WHERE condition, an absolute NO-NO for a DBA :-):
;WITH
cteTally10 AS (
SELECT 0 AS tally UNION ALL SELECT 1 UNION...
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 14, 2014 at 2:30 pm
I strongly agree that you should substitute numeric values in place of the (very) long varchar key columns: [SubscriberID],[UserID] and [DeviceID].
But, until then, based on what you've posted, you should...
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 14, 2014 at 1:54 pm
Not sure specifically what you're looking for, maybe this can help:
;WITH
cteTally10 AS (
SELECT 0 AS tally UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL...
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 14, 2014 at 1:05 pm
Ed Wagner (7/14/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".
July 14, 2014 at 12:31 pm
A computed age column could not be persisted in any event because it uses a non-deterministic value (todays_date).
Whether you use the MONTH+DAY version or the -YEAR version could be based...
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 14, 2014 at 11:53 am
For (very) large tables, typically you do any massive updates in batches if at all possible. Do them by clustered key range; that is, start at a given cluster...
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 14, 2014 at 11:37 am
philosophaie (7/14/2014)
I have a table: Employees with a column: DepartmentID.
I have another table: Departments with a column: ID that...
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 14, 2014 at 11:34 am
Not directly, but you could easily generate that code using sys.columns. I strongly suggest avoiding the INFORMATION_SCHEMA views, as they are less complete as well extremely slow and tending...
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 11, 2014 at 4:34 pm
If possible, please attach the actual query plan xml as an xml file, rather than just a picture of the plan. There are row counts and other stats available...
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 11, 2014 at 11:41 am
fillfactor = 80 is rather low. What did you base that on?
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 11, 2014 at 11:38 am
sgmunson (7/10/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".
July 10, 2014 at 4:09 pm
By far the biggest tuning pay back is to get the best clustering index on all tables, esp. large ones. Until you do that, the rest is honestly pretty...
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 10, 2014 at 3:28 pm
Viewing 15 posts - 5,956 through 5,970 (of 7,613 total)