September 24, 2016 at 9:27 am
Eric M Russell (9/23/2016)
Brandie Tarvin (9/23/2016)
Eric M Russell (9/23/2016)
Brandie Tarvin (9/22/2016)
Eric M Russell (9/16/2016)
If this is something like a data warehouse, where you have very large tables that are sourced for reporting purposes from other transactional databases, then consider more of a Kimball style Star Schema data model rather than a third normal form model.Tis a vended OLTP database that I'm pulling data out of, unfortunately. It's part of ETL to a SAP-type system.
In that case, consider re-architecting your ETL process as SSIS packages. It has builtin tasks for parallel workflow, slowly changing dimensions, error row flow redirection, auditing, etc.
It's actually is in an SSIS package. When SSIS first came out, I found I got the best performance by doing data pulls with Execute T-SQL tasks calling stored procedures. We use other tasks in the package too, but it's the procs / T-SQL code I'm most interested in cleaning up. I'm using the most basic SELECT / INSERT / UPDATE code with temp tables, and two of the procs are making some use out of CTEs. That's about it. Mega table joins to get the data, though.
I'm not sure if MERGE will help me or not. I know in some instances using CTEs caused worse performance than what I was already doing, so I don't want to go CTE heavy just to use "the latest and greatest."
Basically, I have some time to tinker and check performance with new code features. If I can clean up pages of code into more compact modules using more recent T-SQL functionality, then great! But if that new functionality is going to add complexity to my monster code without really giving a performance boost, I'll probably leave it as is. So, again, if anyone has any thoughts about their favorite 2k8 and up T-SQL features, please let me know so I can do some research while I have time to focus on this project.
Regardless of SQL Server version, performance optimization should start by looking at execution plans.
Agreed...almost. 😉 I think you should start by taking some benchmarks of what you have. Go as detailed as you can so you can assess each step of the process and the process as a whole. Then, start looking at your execution plans, reads, non-SARGable predicates, implicit casts, CTEs used to divide-and-conquer, BULK INSERT opportunities, etc., etc., etc. And since you're looking at things developed against SQL 2000, don't discount the power of the windowing functions. I don't know what you're doing, but they can be very performant.
I don't know about you Brandie, but I tend to get a lot of job satisfaction out of performance tuning. I love it when I get a 30-minute process down to a couple of seconds. Enjoy it and have fun. 😛
September 26, 2016 at 9:01 am
ScottPletcher (9/23/2016)
Eric M Russell (9/23/2016)
Regardless of SQL Server version, performance optimization should start by looking at execution plans.
Not necessarily. If the best clustered indexes are not already in place, you should start by looking at all index stats; you might need to look at "heavy" queries as a part of that, but not always.
You need to look at the execution plan to see what tables are referenced, and then look at statistics on those tables and indexes.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 26, 2016 at 9:47 am
Eric M Russell (9/26/2016)
ScottPletcher (9/23/2016)
Eric M Russell (9/23/2016)
Regardless of SQL Server version, performance optimization should start by looking at execution plans.
Not necessarily. If the best clustered indexes are not already in place, you should start by looking at all index stats; you might need to look at "heavy" queries as a part of that, but not always.
You need to look at the execution plan to see what tables are referenced, and then look at statistics on those tables and indexes.
Initially, you could just look at the stats of all significant tables and indexes. And unless you've already reviewed the clustered indexes on all tables, that's what you should 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".
September 26, 2016 at 10:51 am
ScottPletcher (9/26/2016)
Eric M Russell (9/26/2016)
ScottPletcher (9/23/2016)
Eric M Russell (9/23/2016)
Regardless of SQL Server version, performance optimization should start by looking at execution plans.
Not necessarily. If the best clustered indexes are not already in place, you should start by looking at all index stats; you might need to look at "heavy" queries as a part of that, but not always.
You need to look at the execution plan to see what tables are referenced, and then look at statistics on those tables and indexes.
Initially, you could just look at the stats of all significant tables and indexes. And unless you've already reviewed the clustered indexes on all tables, that's what you should do.
Especially when working with 3rd party source databases, we really don't know which tables are "significant" unless we analyze the SQL or execution plans. I know some of us have strong opinions regarding identity vs. natural clustering keys... but let's not rehash that discussion. I think we can all agree that generally speaking database performance optimization boils down more to physical data modeling and basic SQL optimization rather than leveraging new T-SQL features introduced in the latest release.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 26, 2016 at 11:04 am
Eric M Russell (9/26/2016)
ScottPletcher (9/26/2016)
Eric M Russell (9/26/2016)
ScottPletcher (9/23/2016)
Eric M Russell (9/23/2016)
Regardless of SQL Server version, performance optimization should start by looking at execution plans.
Not necessarily. If the best clustered indexes are not already in place, you should start by looking at all index stats; you might need to look at "heavy" queries as a part of that, but not always.
You need to look at the execution plan to see what tables are referenced, and then look at statistics on those tables and indexes.
Initially, you could just look at the stats of all significant tables and indexes. And unless you've already reviewed the clustered indexes on all tables, that's what you should do.
Especially when working with 3rd party source databases, we really don't know which tables are "significant" unless we analyze the SQL or execution plans. I know some of us have strong opinions regarding identity vs. natural clustering keys... but let's not rehash that discussion. I think we can all agree that generally speaking database performance optimization boils down more to physical data modeling and basic SQL optimization rather than leveraging new T-SQL features introduced in the latest release.
The stats themselves will tell you which tables are significant. Indeed, for a 3rd party app, I wouldn't rely on anything other than the actual stats.
My overall point is that it is a mistake to first "tune" query by query. That usually devolves into creating a covering index for (virtually) every query. Then, over time, as a new column is added to each query, the size of the covering indexes grows and grows. Typically they reach anywhere from 75% to 400% of the original table size. From my experience, you're vastly better off getting the best clustered index first, because that in and of itself reduces the number of problem queries and the overall overhead significantly, usually drastically.
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".
September 27, 2016 at 4:55 am
I'll keep this all in mind. Thanks, everyone.
Viewing 6 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply