Forum Replies Created

Viewing 15 posts - 931 through 945 (of 7,619 total)

  • Reply To: Performance related issue after using left join to bring new fields to the query

    You can clean up the existing code some, but the condition:

    Prop_TYPE NOT LIKE '%Rond%'

    is still going to require a table scan or a covering index scan (if a covering index...

    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".

    April 6, 2022 at 11:21 pm

    #4011891

  • Reply To: Create constraint using index

    I don't believe SQL will use an existing index for a constraint, even if the index perfectly matches the constraint to be added.

    In your situation, the index and the constraint...

    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".

    April 6, 2022 at 8:25 pm

    #4011815

  • Reply To: Measuring Ad-hoc workload on a per database level

    (1) Did you set on the "optimize for ad-hoc workloads" SQL Server setting?

    (2) What is the "threshold for parallelism setting"?  In particular, did you leave it at the default of...

    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".

    April 5, 2022 at 5:35 pm

    #4011172

  • Reply To: Trying to re-id a database table and getting error on the first select keyword

    Mr. Brian Gale wrote:

    I just wanted to add in that I am not a big fan of the "SELECT * INTO" approach to table creation.  It recreates the table, but only 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".

    April 5, 2022 at 5:22 pm

    #4011166

  • Reply To: Slow Sql server

    Typically I/O causes the most issues.

    Therefore, another first quick thing to do is to look at the queries with the TOP (10) Avg I/O.  You can find these from SSMS.

    Right-click...

    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".

    April 4, 2022 at 6:07 pm

    #4010640

  • Reply To: Stored Procedure Performance

    Sorry, I wasn't being clear before about how to use transactions here.

    If there are no BEGIN TRAN / COMMIT TRAN, then every modification (DELETE / INSERT / UPDATE) will 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".

    April 4, 2022 at 3:26 pm

    #4010569

  • Reply To: Find string and stop there

    I'd urge you not to write a query based only on current data, but to make it more flexible:

    --
    insert into #tbl_backup_path values ('\\x.x.x.x\Database_Backup_Daily\ServerName$SQLEXPRESS\DB_5\DIFF\ServerName$SQLEXPRESS_DB_4_FULL_20220327_091554.diff')


    SELECT backup_path, LEFT(backup_path, LEN(backup_path) -...

    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".

    April 4, 2022 at 2:31 pm

    #4010545

  • Reply To: Update a sql_variant column

    mattias.palm1 wrote:

    Where does the data type int come from?

    //Naper72

    It comes from the "ELSE 0", as ratbak implied.  The "0" by itself is a numeric.  Use "'0'" instead.

    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".

    April 4, 2022 at 2:25 pm

    #4010544

  • Reply To: Converting varchar to Int for date difference

    Btw, I think it would be better if you used a separate column for the string message to keep the diff column as only numeric, but that's just IMO.

    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".

    April 4, 2022 at 2:22 pm

    #4010543

  • Reply To: SQL query is taking 9 hours to complete

    (1) You need to verify that the 3 values are unique on at least one table; otherwise you will get the equivalent of a CROSS JOIN for all non-unique groups. ...

    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".

    April 4, 2022 at 2:03 pm

    #4010537

  • Reply To: Best Method for

    If you do not need full-text indexing nor filestream data on the reporting db, you could consider using a db snapshot as "DB2".  The initial create of the snapshot is...

    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".

    March 31, 2022 at 2:07 pm

    #4008860

  • Reply To: Stored Procedure Performance

    cmartel 20772 wrote:

    In an earlier post, Scott suggested to add BEGIN TRANSACTIONS and COMMIT TRANSACTIONS. As expected this only makes the procedure run much slower.

    I suggested to add them to BLOCKS...

    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".

    March 31, 2022 at 1:45 pm

    #4008853

  • Reply To: Stored Procedure Performance

    I agree that you'd most likely be better off using a single UPDATE for specific conditions whenever possible.

    However, if that's not feasible at this time, other possibilites are:

    (1) Add BEGIN...

    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".

    March 29, 2022 at 6:23 pm

    #4007799

  • Reply To: Remove some text

    Not sure what you mean.  It did remove 'rtf' from the string.  What more, specifically, do you want it to do?

    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".

    March 28, 2022 at 8:14 pm

    #4007279

  • Reply To: Trigger: Need help to save activity log on delete

    Michael L John wrote:

    ScottPletcher wrote:

    As to putting FKs on an audit table... don't EVER do that.  There's not reason to because the rows have already survived the INSERTs and UPDATEs for FKs...

    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".

    March 28, 2022 at 8:07 pm

    #4007277

    • Viewing 15 posts - 931 through 945 (of 7,619 total)