Viewing 15 posts - 5,971 through 5,985 (of 7,619 total)
philosophaie (7/14/2014)
I have a table: Employees with a column: DepartmentID.
I have another table: Departments with a column: ID 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".
July 14, 2014 at 11:34 am
Not directly, but you could easily generate that code using sys.columns. I strongly suggest avoiding the INFORMATION_SCHEMA views, as they are less complete as well extremely slow and tending...
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 11, 2014 at 4:34 pm
If possible, please attach the actual query plan xml as an xml file, rather than just a picture of the plan. There are row counts and other stats available...
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 11, 2014 at 11:41 am
fillfactor = 80 is rather low. What did you base that 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".
July 11, 2014 at 11:38 am
sgmunson (7/10/2014)
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 10, 2014 at 4:09 pm
By far the biggest tuning pay back is to get the best clustering index on all tables, esp. large ones. Until you do that, the rest is honestly pretty...
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 10, 2014 at 3:28 pm
I must be missing something, because with the limited search criteria so far it looks straightforward. I can't tell whether they will pass in a single search id 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".
July 10, 2014 at 3:25 pm
If you do many lookups by ManagerID, you'd likely be able to avoid messing with all this by clustering the table differently:
CREATE TABLE Employees (
ID INT...
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 10, 2014 at 3:09 pm
You should also be able to use a CROSS APPLY to a "SELECT TOP (1) ..." to get the most recent row for any given user.
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 10, 2014 at 2:51 pm
I want to be a successful DBA someday and hopefully can write sql scripts without looking or using BOL. 🙂
I've been a SQL Server DBA for 13+ years and 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".
July 10, 2014 at 11:33 am
SELECT
COALESCE(t1.Account, t2.Account) AS Account,
MAX(Date) AS Date
FROM (
SELECT Account, MAX(Date) AS Date
FROM dbo.table1
...
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 10, 2014 at 11:30 am
The single biggest factor in overall query performance is getting the best clustered index on each table (hint: it's rarely an identity column). Typically you'd start with a higher...
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 9, 2014 at 10:24 am
I'm virtually certain the tables are clustered improperly. That should be fixed before you do the partitioning. It's even possible you won't need to partition if you put...
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 7, 2014 at 1:05 pm
You can, and definitely should, avoid converting cStaging.cDate if you can.
--if "cDate" is a datetime column:
--instead of this:
--WHERE (CONVERT(DATE, cStaging.cDate) BETWEEN @startdate AND @endDate) AND (CONVERT(DATE,...
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 7, 2014 at 12:40 pm
sqldriver (7/5/2014)
TheSQLGuru (7/1/2014)
create index ix_Split_1 on #Split_1 (NewParameter)Good call on the joining to a split-built derived table and how putting that into a temp table could make things faster. ...
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 6, 2014 at 12:52 am
Viewing 15 posts - 5,971 through 5,985 (of 7,619 total)