Viewing 15 posts - 6,061 through 6,075 (of 7,619 total)
First, as always, verify that you have the best clustered index on each table.
For example, just from a very quick look, table:
dbo.DCSubRegionTran
should have:
DCSRFiscYear
as its first clustering column, assuming you (nearly)...
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".
May 30, 2014 at 10:17 am
I'd urge you to add a bit flag to the PROD table to indicate whether a given row has 7 consecutive valid flow values or not, rather than recomputing every...
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".
May 28, 2014 at 2:23 pm
SELECT
ISNULL(sales.ItemCode, returns.ItemCode) AS ItemCode,
ISNULL(sales.total_sales, 0) - ISNULL(returns.total_returns, 0) AS NetSales
FROM (
SELECT T0.ItemCode, SUM(T0.LineTotal) as total_sales
...
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".
May 27, 2014 at 11:55 am
sirishgreen (5/23/2014)
I took your advice:these dates result in an error:
@dtStart='20140418',@dtEnd='20140418'
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Thanks
Simon
Then the error must be...
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".
May 23, 2014 at 8:55 am
Just in general, if you (almost) always query table* "v0_AccountSecuritiesBASEL_III" by DataDate, then you should strongly consider clustering the table on that date first, rather than on -- my best...
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".
May 22, 2014 at 3:31 pm
Instead of changing the code like that, just remove the UPDATE STATS statement entirely. You shouldn't be doing that all throughout code anyway.
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".
May 22, 2014 at 1:44 pm
jmoorhead14 (5/22/2014)
DECLARE @spid int
SELECT
@spid = MIN(l.request_session_id)
FROM
sys.dm_tran_locks l
INNER JOIN sys.dm_tran_active_transactions t...
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".
May 22, 2014 at 1:39 pm
If you're on Enterprise Edition you can add "ONLINE = ON" and the table can still be used while the index is being built.
I prefer to always explicitly specify 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".
May 22, 2014 at 11:15 am
Sample data in a consumable format would be most helpful.
An example of what I am trying to get.
guid, 2014-05-10 01:04, 2014-05-10 01:14
I'm pretty sure my query gives you 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".
May 22, 2014 at 10:11 am
Hmm, I expected my code to give you that.
Can the same guid appear more than once??
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".
May 21, 2014 at 8:02 am
Instead of disabling the trigger, which would affect all row updates, of course, you might want to consider using CONTEXT_INFO() to selectively bypass trigger processing just for your updates.
For example:
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".
May 20, 2014 at 1:33 pm
Maybe this; not 100% sure of the final output you want to see:
select x.guid, x.max_intime, x.max_outtime
from
(
select guid,
max (case when gatestatus = 1 then Gatetime else...
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".
May 20, 2014 at 1:13 pm
Here's another version. It uses no extra tables and makes no assumptions: start date and/or end date can be on a weekend, and all SQL settings, including language, are...
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".
May 19, 2014 at 5:04 pm
At a minimum, you need to look at missing index stats and usage stats. (Beyond min, you would include sys.dm_db_index_operational_stats.)
Below are queries that will list what you...
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".
May 19, 2014 at 11:03 am
Look for triggers on the underlying tables. For example, some locations prefer so-called "soft deletes", where the row is never actually deleted, but instead a "deleted" flag gets set....
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".
May 19, 2014 at 10:57 am
Viewing 15 posts - 6,061 through 6,075 (of 7,619 total)