Viewing 15 posts - 5,371 through 5,385 (of 7,613 total)
There is no such thing as an accurate, global freespace percentage. Freespace needs to be set index by index. Thus, a generic rebuild should never specify a fillfactor,...
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".
February 17, 2015 at 8:31 am
Trim functions deal only with spaces. The easiest way to take care of all whitespace is to use REPLACEs, something like this:
LEFT OUTER JOIN LedgerGroups lg 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".
February 12, 2015 at 9:08 am
Verify that the db does not have autoclose or autoshrink set to 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".
February 11, 2015 at 12:09 pm
I'd shrink the log file:
DBCC SHRINKFILE ( 2, 4096 )
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".
February 10, 2015 at 4:20 pm
Rick Todd (2/10/2015)
Nevyn (2/10/2015)
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".
February 10, 2015 at 3:02 pm
Also, verify that you have enough free log space on that db already allocated to handle all the deletes.
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".
February 10, 2015 at 2:22 pm
Make sure you have nonclustered indexes on:
transaction_hdr ( float_no )
transaction_tenders ( float_no )
You want SQL to scan the floats table, not use an index, since it will delete > 70%...
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".
February 10, 2015 at 2:21 pm
You might want to consider putting each client schema in a different db, then using synonyms in the main db to point to those objects. It will look 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".
February 10, 2015 at 11:01 am
;WITH cteCustRecentOrders AS (
SELECT
AA.[CustomerCode],
AA.[OrderDate],
AA.CustomerProductID,
AA.Rank_ID
FROM (
...
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".
February 9, 2015 at 12:40 pm
Luis Cazares (2/6/2015)
Scott,I mainly agree with you, but the OP stated that there's always one Payroll_Year at a time. Based on that, I wouldn't include it in the index.
True, I...
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".
February 6, 2015 at 12:38 pm
jdbrown239 (2/6/2015)
I have 15 GB allocated space and 98% free
In the log file?
Yeah, that should be plenty, so the statement should run as fast it can.
Are there triggers, replication or...
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".
February 6, 2015 at 10:28 am
I'd cluster the table by:
Payroll_Year, PayPeriod, EmployeeID and, optionally, CompanyID
That way the clustering key is generally increasing, which will reduce fragmentation. That's not the main criterion for a clus...
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".
February 6, 2015 at 9:32 am
That statement is going to have the scan the table regardless of what you do.
The critical thing for performance is to pre-allocate (and thus pre-format) enough log space 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".
February 6, 2015 at 9:23 am
Yes. Why risk not recompiling it?
Get the query plan id and remove all plans related to this proc from the cache using:
DBCC FREEPROCCACHE { plan_handle }
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".
February 5, 2015 at 2:23 pm
JJR333 (2/5/2015)
The first NOT clause makes all the difference.
Is this T-SQL logic different from say Excel VBA logic?
SUM the turnover of all stores NOT...
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".
February 5, 2015 at 1:07 pm
Viewing 15 posts - 5,371 through 5,385 (of 7,613 total)