SQL Server 2014 Checklist for Performance

Question

SQL Server 2014 Checklist for Performance

Viewing 15 posts - 31 through 45 (of 50 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

Sorry if I am repeating what anyone else said - I have not had a chance to read through all the comments. Here's my 2 cents:

Normalize the data as it benefits performance?

This is true sometimes but certainly not in my Data Warehouse! Perhaps you should specify that OLTP environments are better when they are normalized and OLAP environments are better denormalized.

Indexes work better on intergers?

They work even better on Bits! Perhaps your should expand on this a little...

Don't create too many indexes (depends)

Again, it's okay in my data warehouse provided the indexes are being used... Perhaps it would be better to point out that the fewer indexes the better when inserting, updating and modifying data...

More are better when you are Reading data... Unused indexes are pretty much always bad.

I would add that, when you are loading a DW drop indexes and constraints then recreate after your load.

Rebuilt cluster indexes

... when they have highly fragmented, Reorg when they are slightly fragmented (I forgot the recommendation, I think it's 15%-30% re-org, 30%+ rebuild).

Don't use NOLOCK

This is not a performance thing, it's more of "how to get the correct answer" thing...

Use table variables instead of temp tables

... except in a few cases I would respectfully disagree. I would put this to the test if I were you.

Multi statement table valued functions are very bad!

... Yep! I would add that so are Scalar Valued functions. Perhaps add that you should use inline table valued functions instead

For Scalar you could use Inline Scalar Valued Functions.

This article does not discuss anything about 2014 specific performance tips such as in-memory or improved SELECT INTO or how to take advantage of the new 2014 Cardinality Estimator.

All that said, good work. These type of articles (or checklists) are very valuable.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

pkapas SSC Veteran Points: 209 More actions · Answer 2

Quick question. I read this in the article: "Don't nest the views and join views to views"

If you have to rely on getting data from a previous view in order to proceed with a query in a following view, is there a better way of doing that? I'm new to SQL Server, so I'm still learning. I have a query that relies on a couple of other views, and its about a 5-6 level nested view.

KenVickers Valued Member Points: 60 More actions · Answer 3

Well I did say "can" but they are memory objects and wont create anything in tempdb, admittedly small temp tables will probably behave similarly, and as I alluded to because they are memory only can cause major problems if they become too big.

mauriciorpp Default port Points: 1472 More actions · Answer 4

I decided to close my 2014 blogging year by linking your article, Rudy.

Please take a look at it: https://thelonelydba.wordpress.com/2014/12/30/last-post-of-the-year-sql-server-2014-checklist-for-performance/

Also instead of discussing any general tip, I would like to add two more tips:

? Keep your app data on a single database. Cross-database queries are bad.

? Forget that Linked-Servers exist. Use other technology instead.

What you think of these two?

PS: It would be great if we could post content directly from SSC to WordPress.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

KenVickers (12/29/2014)
Well I did say "can" but they are memory objects and wont create anything in tempdb

Myth, completely false.

Table variables are not memory-only, they are allocated space in TempDB, they can and do get persisted to disk. In fact, in most ways around storage they behave the same as temp tables do.

http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/

http://www.sqlservercentral.com/articles/Temporary+Tables/66720/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

mauriciorpp (12/30/2014)
Keep your app data on a single database. Cross-database queries are bad.

Nothing wrong with cross-database queries. Performance-wise they're the same as queries within a database. There may be some security amusement. There are good, valid reasons to split an app across multiple databases. With database mirroring, doing so meant problems in a failover, but that's been solved with availability groups.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rudy Panigas SSChampion Points: 10702 More actions · Answer 7

mauriciorpp (12/30/2014)
I decided to close my 2014 blogging year by linking your article, Rudy.
Please take a look at it: https://thelonelydba.wordpress.com/2014/12/30/last-post-of-the-year-sql-server-2014-checklist-for-performance/
Also instead of discussing any general tip, I would like to add two more tips:
? Keep your app data on a single database. Cross-database queries are bad.
? Forget that Linked-Servers exist. Use other technology instead.
What you think of these two?
PS: It would be great if we could post content directly from SSC to WordPress.

Closing your blog with my article is nice and thank you. When I link to articles from my blog I mention the original author's name in blog to help promote the author.

I have seen app data on several databases that made sense and work extremely well. The same with Linked-servers. Personally I would not discourage the use of these but if you are just be aware that code should be well written and don't use this for everything.

Thanks and Happy New Year

Rudy

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 8

Ah... dang it! I forgot to do one of the most important things there is for the new year... change the bands on the pork chop launcher! 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul Brewer SSCrazy Points: 2955 More actions · Answer 9

Hi Gail,

I spent a couple of hours last night using this stress testing tool SQLQueryStress to compare temp table\table variable latch contention. You were right, I could not find much difference between the two table types for DML or DDL latch contention. The tests were very basic, inserts into temp table / table variable, both were in constant PAGELATCH waits for 2:1:x (where x is 1 or a multiple of 8088).

Best wishes

Paul

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Paul Brewer (1/3/2015)
Hi Gail,
I spent a couple of hours last night using this stress testing tool SQLQueryStress to compare temp table\table variable latch contention. You were right, I could not find much difference between the two table types for DML or DDL latch contention. The tests were very basic, inserts into temp table / table variable, both were in constant PAGELATCH waits for 2:1:x (where x is 1 or a multiple of 8088).
Best wishes
Paul

Awesome, thanks for doing those tests.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DennisPost SSCrazy Points: 2691 More actions · Answer 11

pkapas (12/29/2014)
Quick question. I read this in the article: "Don't nest the views and join views to views"
If you have to rely on getting data from a previous view in order to proceed with a query in a following view, is there a better way of doing that? I'm new to SQL Server, so I'm still learning. I have a query that relies on a couple of other views, and its about a 5-6 level nested view.

We also use multiple levels of nested queries, some indexed, most not.

The amount of logic and length of the views makes nesting a good option for us. This goes against the grain of some peoples opinion about it being harder to read. This is true when you start working with them, but as you come to realize the same views are getting nested, reading and understanding the code becomes much easier.

There might be a better way, of course that depends. We don't have the time to put the data into a data warehouse, nor would we want to as our data changes frequently.

Here are some helpful articles that point out the evils of nested views.

The Seven Sins against TSQL Performance, Grant Fitchity[/url] (Must read!)

What Are Your Nested Views Doing?

Is nested view a good database design?

Nested views causing query performance woes....[/url]

mauriciorpp Default port Points: 1472 More actions · Answer 12

GilaMonster (12/31/2014)Nothing wrong with cross-database queries. Performance-wise they're the same as queries within a database. There may be some security amusement. There are good, valid reasons to split an app across multiple databases. With database mirroring, doing so meant problems in a failover, but that's been solved with availability groups.

Gail, I have seen cases of pretty busy OLTP dababases where adding a single table from an outside DB could wreak havoc to performance. It was a critical login piece, and one DB was several GB in size (600GB or so), while the other was only a few MB.

We isolated the problem to be caused by this join: any query on it would take a few seconds to run, and this impacted the end-user directly. We then created a staging table on the big DB and seconds became milliseconds. Another process would take care of updating that stage table as necessary.

I could not continue testing this on the live system (the test environment can't be taxed like the live servers - too complex load) so I took this as a guideline - better safe than sorry.

mauriciorpp Default port Points: 1472 More actions · Answer 13

Rudy Panigas (12/31/2014)
Closing your blog with my article is nice and thank you. When I link to articles from my blog I mention the original author's name in blog to help promote the author.
I have seen app data on several databases that made sense and work extremely well. The same with Linked-servers. Personally I would not discourage the use of these but if you are just be aware that code should be well written and don't use this for everything.
Thanks and Happy New Year

hi Rudy. That's a nice idea, I will start to mention the author's names and probably a bit more detail than "read this" 😀 I'm starting by adding your info.

I was thinking about my additions to your list as discussed with Gail and you. Maybe I have a few more battle scars caused by these two issues than most, and the problems I faced were in already existing (and problematic) servers. All the servers that I setup from the ground never had this kind of behaviour, but anyway I like to avoid cross-db and linked-servers.

My case with linked-servers is due to crazy alias naming. It allows you to call anything with a name completely different from the real name, so we may think that we are getting data from server A but see server B instead. And this happened to me, of course, I had to recreate a lot of linked-servers and all security settings to stop bad data from entering to the DB.

of course both can be usefull in some situations, but I avoid them because... well, I'm avoiding fatigue!

Rudy Panigas SSChampion Points: 10702 More actions · Answer 14

Glad to see that my article has created much discussion. I would like to point out that my checklist is "My" checklist and is no way complete. I appreciate all the comments and opinions from many people here 🙂

Thanks again to everyone

Rudy

jeffem Ten Centuries Points: 1232 More actions · Answer 15

mauriciorpp (1/6/2015)
GilaMonster (12/31/2014)Nothing wrong with cross-database queries. Performance-wise they're the same as queries within a database. There may be some security amusement. There are good, valid reasons to split an app across multiple databases. With database mirroring, doing so meant problems in a failover, but that's been solved with availability groups.
Gail, I have seen cases of pretty busy OLTP dababases where adding a single table from an outside DB could wreak havoc to performance. It was a critical login piece, and one DB was several GB in size (600GB or so), while the other was only a few MB.
We isolated the problem to be caused by this join: any query on it would take a few seconds to run, and this impacted the end-user directly. We then created a staging table on the big DB and seconds became milliseconds. Another process would take care of updating that stage table as necessary.

And this was all running on the same instance? I have never seen cross-db queries cause any detectable issues myself, though of course cross-server queries can be incredibly bad. I still have developers create cross-server joins, despite having demonstrated how bad it is and how to work around it.