Viewing 15 posts - 4,621 through 4,635 (of 7,619 total)
I believe it may be due to the deferred resolution SQL uses for permanent tables that don't exist during parsing. Then again, maybe not :blink:
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".
November 24, 2015 at 2:04 pm
Make sure you have the best clustered index on every table. That is critical for best performance overall, and the wrong clustering key often leads to extra 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".
November 24, 2015 at 2:01 pm
As noted, msdb is the main concern here.
But it's possible you might need to do rebuilds in model if you add/adjust tables in there, especially since you'd want the model...
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".
November 24, 2015 at 10:25 am
There are legitimate cases for using identity as a later key.
For example, in an OrderItems table, I might want to key/cluster the table on ( OrderNumber, $IDENTITY ) in 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".
November 24, 2015 at 9:48 am
The bigger design error is likely that you have PK identities when the table contains a valid data key. Identities have become a crutch and are vastly over-used. ...
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".
November 24, 2015 at 9:00 am
Here's code that will generate the code you need.
Press Ctrl-T before running the code to have it come out as text. Then copy that text to a new query...
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".
November 23, 2015 at 3:17 pm
I don't think a global temp table would necessarily be immediately dropped. But overall I agree, there's no great concern to drop temp tables in the code, unless perhaps:
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".
November 23, 2015 at 12:45 pm
That's extremely bizarre. They can just do:
WHERE pk = '10000'
and if pk is a numeric type, SQL will convert the 10000 to that type and be able to do...
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".
November 23, 2015 at 12:40 pm
I'm not talking about partitioning, I'm talking about index tuning, particularly first selecting the best clustered index. Most tables don't really need partitioned. I partition mainly to allow...
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".
November 20, 2015 at 2:30 pm
You could also archive first and modify the indexes later.
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".
November 20, 2015 at 1:48 pm
The main thing is to make sure the tables are properly clustered. Often that will give you good performance from the table even without archiving (or partitioning).
Partitioning is sometimes...
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".
November 20, 2015 at 12:17 pm
--!!NOTE: showing missing indexes can take some time; set @list_missing_indexes = 0 below if you don't want to wait!!
--USE [your_db_name]
SET DEADLOCK_PRIORITY -8 --make sure we are the victim if we...
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".
November 20, 2015 at 12:08 pm
Would need to see the view definition and related table definitions.
If you're serious about tuning the tables, rather than just this specific query, would also need to see...
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".
November 20, 2015 at 11:48 am
First, let's clear this up:
Is it only the user account of the one who made the [transaction], who gets [blocked]?
Absolutely not. SQL doesn't care at all about who did...
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".
November 20, 2015 at 10:38 am
Sergiy (11/19/2015)
ScottPletcher (11/19/2015)
I'm certain they're never running an EXISTS check because they are not reusing codes,
Have a look at the topic starter:
SET @RedemptionCode = dbo.GenerateRandomBase32(@RedemptionSeed)
WHILE((@RedemptionCode IS NULL) OR EXISTS(SELECT RedemptionCode...
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".
November 19, 2015 at 3:53 pm
Viewing 15 posts - 4,621 through 4,635 (of 7,619 total)