Viewing 15 posts - 1,561 through 1,575 (of 7,613 total)
For performance, first try converting it into an inline TVF; those get compiled directly into the code whereas a multi-line TVF does not.
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
GO
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 18, 2021 at 4:18 pm
D'OH, sorry, didn't realize SQL wouldn't let you OFFLINE the db in that case. But, one of the reasons I avoid SINGLE_USER like the plague is the issues it causes.
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 18, 2021 at 4:07 pm
You need to make sure that TABLOCK is specified on the load so that you get minimal logging. I'm not 100% sure how to do that via SSIS but look...
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 18, 2021 at 4:04 pm
Again, I'd say create the clustered index before loading the table. Use row compression, since that could save some I/O which would reduce the time needed.
Are other processes using "SELECT...
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 18, 2021 at 8:23 am
SQL does not like that where statement line.
What specifically do you mean by that? What error message did you get?
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 18, 2021 at 8:07 am
CREATE TABLE ##temp_table_order#s ( id int NOT NULL PRIMARY KEY, data_field varchar(8000) );
INSERT INTO ##temp_table_order#s
SELECT id, data_field
FROM ##temp_table
WHERE data_field LIKE 'Order #%'
SELECT
Order_No,
...
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 18, 2021 at 8:03 am
I've always liked the idea of assigning a unique standard alias for every table and always using it. You can add a 1, 2, etc., if you need to use...
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 17, 2021 at 10:47 pm
No, you can miss user(s) of the db that way. Instead, force the db offline; then bring it back online and immediately USE the db yourself and then set 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".
May 17, 2021 at 6:32 pm
FROM
dbo.fnGet_NAME] (@processDate) -- This function is hitting a very big table and I do see some missing IDX hints, however, apart from IDX's I wanted to find out more...
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 17, 2021 at 6:26 pm
Hmm, interesting. For the nonclustered index(es)? For a clus index create, I could see delays as SQL would have to sort the entire contents of the table.
What is the 'cost...
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 17, 2021 at 3:00 pm
1. The source is another table in another database and I am using a dataflow task to move it from one table to another. The source is a Stored procedure
There...
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 17, 2021 at 2:51 pm
Best would be a CHECK constraint if that is possible. You wouldn't have to write the code and SQL would guarantee that it gets run and works without error (given,...
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 15, 2021 at 3:32 am
I guess you have to fall back on querying the msdb.dbo.restorehistory table every nn minutes and looking up the details on all newly restored dbs, since the last check nn...
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 14, 2021 at 6:46 pm
Rats! Apparently RESTORE does not trigger a CREATE DATABASE event and there is no RESTORE DATABASE event, at least from what I've found so far.
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 14, 2021 at 6:38 pm
If a RESTORE DATABASE triggers a "CREATE DATABASE" event, and I'm not sure about that although it seems logical, then you could use a DDL trigger to check 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".
May 14, 2021 at 2:58 pm
Viewing 15 posts - 1,561 through 1,575 (of 7,613 total)