Viewing 15 posts - 1,231 through 1,245 (of 7,613 total)
Database and index reviews cannot be automated. No software now does even a reasonably decent job of doing that. It must be done by a person.
Especially important is determining and...
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".
October 3, 2021 at 5:48 am
That's all the biggest parts of it.
Sometimes you can also need data cardinality info, that is, how many different values / row counts there are for certain columns.
For example, say...
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".
October 2, 2021 at 4:22 am
I have to admit, I really don't know how to debug "it doesn't like it." I'm not even 100% sure what that specifically means.
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".
October 2, 2021 at 4:19 am
ScottPletcher wrote:(Code reformatted but so that it can be read without scanning 50 feet to the right.)
50 feet? Is your browser set to Mr Magoo mode?
Hyperbole.
But, c'mon man, an...
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".
October 1, 2021 at 5:20 pm
Start with data modeling first, so not with "tables"/"columns" but with entities and attributes. Spend some time getting a full list of all data attributes (elements, "columns") that you need...
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".
October 1, 2021 at 4:18 pm
The key thing for performance is likely to be the indexing, and here specifically the clustered indexing.
Both IPS and V should be clustered on ( LocNum, InOrOut, AccountNumber ), in...
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".
October 1, 2021 at 3:46 pm
That code should return exactly the same datetime range as your original code. If not, please post what strings your current code is returning for a couple of rows against...
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".
September 30, 2021 at 7:35 pm
Purely from a performance perspective, I think you'd be better off using another table -- CustomerRanges -- to reduce each range to a single number for the final lookup in...
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".
September 30, 2021 at 7:33 pm
First, get rid of the functions against table columns in the WHERE clause. Those are always a terrible idea for performance. (Code reformatted but so that it can be read...
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".
September 30, 2021 at 4:44 pm
Jeffrey Williams wrote:You should also not use SELECT to set the variable - use SET instead:
WHY? What do you base that recommendation on?
I remember someone testing and saying that 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".
September 30, 2021 at 2:05 am
About the>= vs =, yes, you're correct, it can be = in this example. I didn't mention it, but often we rerun a few days in the back, so 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".
September 30, 2021 at 2:03 am
Just an example of concatenation when using EXEC():
DECLARE @sql1 varchar(1000)
DECLARE @sql2 varchar(1000)
SET @sql1 = 'SELECT GETDATE() AS today'
SET @sql2 = 'SELECT COUNT(*) AS object_count FROM sys.objects'
EXEC(@sql1 + ';' + @sql2)
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".
September 29, 2021 at 7:24 pm
Oh, sorry, I didn't look closely enough, I thought it was EXEC() and not EXEC sys.sp_executesql.
For sp_executesql, you must combine code before running the proc.
DECLARE @sql NVARCHAR(4000)
,@SQL1 NVARCHAR(4000);
SELECT @sql =...
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".
September 29, 2021 at 7:22 pm
You left off the parens (and something to separate the new SELECT from the old command, either a space or a semi-colon):
EXEC sp_executesql (@SQL + ';' + @SQL1)
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".
September 29, 2021 at 7:09 pm
To determine proper clustering (PKs) for the tables, we'd need to know more about how the data is most often processed, by statistics type or entityid or timeperiod range.
However, 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".
September 29, 2021 at 3:40 pm
Viewing 15 posts - 1,231 through 1,245 (of 7,613 total)