Viewing 15 posts - 5,986 through 6,000 (of 7,619 total)
By far the biggest payback is in correcting clustered indexes if the wrong key column(s) were chosen for it. And that alone often allows very large numbers of other...
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 2, 2014 at 8:32 pm
I also make the doc tables "live". That is, the code that created/initialized/set up a new db should use the "documentation" table to determine whether the table is static...
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 2, 2014 at 8:30 pm
TheSQLGuru (7/2/2014)
ScottPletcher (7/2/2014)
TheSQLGuru (7/1/2014)
Very few things in SQL Server land can/will short-circuit.
I think that's over-stated. Most optimizers will short-circuit when they safely can. Presumably later versions of...
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 2, 2014 at 8:27 pm
You did review the stats SQL Server provides on index usage and missing indexes before you made your changes, right?
Edit: What indexes you think SQL will need/use and what 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 2, 2014 at 4:06 pm
TheSQLGuru (7/1/2014)
Very few things in SQL Server land can/will short-circuit.
I think that's over-stated. Most optimizers will short-circuit when they safely can. Presumably later versions of the optimizer...
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 2, 2014 at 4:03 pm
I wouldn't want to put them in a separate schema unless you really had to. Aside from hideous renaming issues (*), you might end up with what seems like...
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 2, 2014 at 3:43 pm
If you want to keep/commit previous activities in the loop, you likely need to explicitly BEGIN and COMMIT a tran for each loop, something like this:
WHILE ...
BEGIN
...
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 2, 2014 at 3:35 pm
is there any possible way to keep the table as it is without effecting the current database performance.
Try clustering the table by datetime (rather than identity, I'm guessing) and specify...
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 2, 2014 at 2:40 pm
cbrammer1219 (7/2/2014)
Can a Index be on the Identity that is created on the insert?
Yes, it can be.
For overall performance, what is really critical is determining the best column(s) for 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".
July 2, 2014 at 11:05 am
Given that it's a single string being split, I don't think there's much to gain by changing that code.
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 2, 2014 at 10:57 am
there are no pk or fk in the tables
Don't care about pk/fk specifically. What indexes currently exist on these tables? Could you add a computed column(s) and create...
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 27, 2014 at 3:12 pm
does the order of predicates in where clause matter?
Not generally. But CASE WHEN conditions are always evaluated serially (as far as returning a result is concerned), so 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".
June 27, 2014 at 2:51 pm
Also, be sure the relevant non-DBAs properly enable IFI and other local/policy/Windows permissions that are needed for SQL accounts, proxy accounts, etc..
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 26, 2014 at 12:25 pm
Yes, you can create a non-clustered index w/o a clustered index, but you (almost) never should.
The cluster key does not have to be defined as unique (SQL will internally make...
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 26, 2014 at 11:40 am
Honestly the DBA should have admin access to the server to have maximum effectiveness.
If you don't, and in particular if you don't have direct directory access/control, be extraordinarily careful before...
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 26, 2014 at 11:35 am
Viewing 15 posts - 5,986 through 6,000 (of 7,619 total)