SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

Featured Script

The Voice of the DBA

SQL Server Tuning Skills

Performance tuning a SQL Server instance, database, or even query is a skill that most of us could improve. In fact, I see that the query performance presentations are often the most popular learning items at any event. I've seen quite a few people asking for more tuning talks in different areas, which is a good thing. I think most SQL Saturdays could schedule a different tuning session every hour, or even repeat the same one a few times, and the room would be mostly full each time.

Learning to look at the various possible problems in a SQL Server is both a science and an art. There are good methods, such as waits and queues, for examining where an issue occurs. There are basic techniques for looking at execution plans and then making changes to indexes or T-SQL. There are different options for queries, SET values, and more that will affect your query or even all your queries. 

Sometimes it seems there might be too many options for a beginner to decide how to begin examining a system, especially one that is having issues. I can't cover all of the issues in this short piece, but I can give you some guidance that might help. First, make sure that you know how to gather information about your issue. We have forums at SQLServerCentral to answer your questions, but you have to do some work. Gail Shaw wrote a great piece on how to post performance problems.

Ultimately, you need to understand the recommendations that we may give. It's helpful to learn about indexes, and the differences in the various types (including Columnstore). You should also have a baseline so that you can measure the impact of your changes. How do you know the problem is fixed? Maybe the user gets lucky with a quicker experience when you tell them you've changed the system, but will they call again soon? That second call might be worse than the first one. Even getting a baseline of the poor performance now will help you understand (and prove) that the system is running better.

You also need to understand what not to tune, and which items to ignore. I thought quite a bit about this after reading Paul Randal's post on wait types to ignore. This is part of his 101 series, which are a good way to start improving your skills. Performance tuning takes practice and knowledge, which takes time to acquire. Even if you're fighting a problem today, try and learn a little something about how you get your system to run better.

I'd also recommend that you look to continue your education over time. Take advantage of those sessions at local events, like user groups, virtual groups, and SQL Saturdays. Take advantage of pre-cons at larger events, which pack lots of learning into a few hours. If you go, take a paper pad, not a laptop, and take lots of notes. If you can't get away, maybe you can lobby for some tuning classes from sqlSkills, Brent Ozar Unlimited, or even self-paced courses on Pluralsight.

Learning to become better at tuning is an ongoing process, and one that requires some investment. Perhaps some money, but definitely some time and practice are necessary. Don't let this be a daunting task, but one that's a journey. Your goal is to get a little better each time you work on this skill, not to be an expert that knows it all. I'm not sure anyone gets to that point, but you can become as good at this skill as any of the presenters you watch. It might take you longer than it took them, but you're also able to build on their work and learn from their knowledge. Take advantage of that opportunity.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums


The Voice of the DBA Podcast

Listen to the MP3 Audio ( MB) podcast or subscribe to the feed at iTunes and Libsyn. feed

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

ADVERTISEMENT
Database DevOps

Database DevOps Demo Webinar

Learn how to automate your database deployments alongside your app code in this free demo webinar. Register now

SQL Monitor

What’s the top challenge faced by SQL Server professionals in 2018?

Learn how 626 SQL Server professionals monitor their estates in our new report on the State of SQL Server Monitoring. Discover the challenges currently facing the industry, and what is coming next. Download your free copy of the report

Featured Contents

 

Stairway to ColumnStore Indexes Level 7: Optimizing Nonclustered Columnstore Indexes

Hugo Kornelis from SQLServerCentral.com

In this level, we will focus on optimization techniques to apply while building the nonclustered columnstore index, which is available in all versions of SQL Server from 2012 up. More »


 

Free eBook: SQL Server Backup and Restore

Press Release from Redgate

In this free eBook Shawn McGehee offers advice on query tuning, cutting stored procedures, and system process design and implementation for high availability. Discover how to perform backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Redgate's SQL Backup tool. More »


 

Power BI – Part 1: Introduction

Additional Articles from SimpleTalk

Power BI has been available for a number of years, but new functionality is added regularly. In this new series from Robert Sheldon, you will learn about the latest developments or get started with Power BI if you are new to it. Robert provides an overview of Power BI, the services and tools available in this article. More »


 

From the SQLServerCentral Blogs - Power BI with Mapbox and Volcanic Ring of Fire

Rayis Imayev from SQLServerCentral Blogs

(2018-May-27) With recent Hawaiian volcano activity, it would be interesting to see how other similar events have shaped our Earth planet.... More »


 

From the SQLServerCentral Blogs - Actual Number of Rows are not always accurate

Klaus Aschenbrenner from SQLServerCentral Blogs

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the... More »

Question of the Day

Today's Question (by Steve Jones):

In Python, how are comments denoted?

Think you know the answer? Click here, and find out if you are right.


We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: Python.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

ADVERTISEMENT

Exam Ref 70-765 Provisioning SQL Databases

Prepare for Microsoft Exam 70-765–and help demonstrate your real-world mastery of provisioning SQL Server databases both on premise and in SQL Azure. Designed for experienced IT professionals ready to advance their status, Exam Ref focuses on the critical thinking and decision-making acumen needed for success at the MCSA level. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

In SQL Server 2017, what are the limits for FKs on a table?

Answer: 10,000 incoming, 253 outgoing

Explanation:

The limits are  253 outgoing and 10,000 incoming.

Ref: Maximum Capacity Specifications - click here


» Discuss this question and answer on the forums

Featured Script

Export diagrams

Jonas Gunnarsson from SQLServerCentral.com

The script generates an import script for SQL Server Management Studio diagrams.
Easy to use, exports all diagrams in current database, to a script. Tip: Place it in your repository.

Remarks

The name of diagrams need to be unique in database. The script don't replace an existing diagram. (may be in next release;)

Verify existing diagram names first! List the database diagram(s) for current database:

select * from sysdiagrams;

Usage

Run the script against database and select result to file.

Note
Objects required to use database diagrams is added for owner, when clicking on folder Database Diagrams in SSMS.

Further reading

References

Revisions

  • 1.0 2018-05-25 First version

More »

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 : SQL Server 2017 - Administration

"Take a tail-log backup" - I'm testing a new backup technology and it has raised a question I thought I'd ask here. When you hear...


SQL Server 2017 : SQL Server 2017 - Development

Fairly straightforward IF question - I do not do too much in the way of creating SQL code so please bear with this 20 year+...

Conversion failed when converting the nvarchar value '/' to data type int. - I have SQL query as following : SELECT   TOP (99.9999999) PERCENT CAST(CAST(LEFT(SpecialCode, CHARINDEX('/', SpecialCode) - 1) AS nvarchar) AS int) AS...

Inner Joins - Hi Friends, I was wondering is there a Rule  of Thumb when I am  joining my tables as it relates...


SQL Server 2016 : SQL Server 2016 - Administration

spn won't register on restart - I've got an issue getting an instance of sql server 2016 sp 1 cu 8 to register the SPN on...

Some users showing in error log as attempting to open a database they do not have access to - We use AD groups, SQL Server 2016. One AD group (with over a dozen users) that doesn't have any permissions/privileges...

Regulary delete and rebuild of table influences perfomance? - Ahoi, Situation: i have a table with around 40 million rows and a clustered primary key set by IDENTITY(1,1). Theres regulary deletes and...


SQL Server 2016 : SQL Server 2016 - Development and T-SQL

How to get and use the value returned by SP to a INSERT INTO... SELECT... statement - Hi, I am just new in SQL language and still studying it. I'm having hard time looking for answer on how...

CTE to returning WAC price - Hello community, I have build  a CTE to calculate my WAC price like that: CREATETABLE stock_table   (document_type VARCHAR(2), document_date datetime, product_id


SQL Server 2012 : SQL 2012 - General

T-SQl, Help with the WHERE CLAUSE ( I want the entire records set as output or just the ones marked in 2 cols ) - My question is listed in the code. Declare @C CHAR(1); Select @C='1' IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t; CREATE TABLE #t(...


SQL Server 2012 : SQL Server 2012 - T-SQL

Performance tuning a transaction - Hello SSC, Ok, I am going to try and explain this. Please bear with me :) I have this stored procedure that...


SQL Server 2008 : SQL Server 2008 - General

Filegroups on different physical drives - Hi there,    Is there a disadvantage of having filegroups of a database on different physical drives? My Database is very...


SQL Server 2008 : SQL Server Newbies

detect if previous record is higher or lower ? - In it's simplest form if I had a table that stored a persons name, date that the record was entered,...


Programming : Powershell

How Do You Connect to a Central Management Server If Your Server is a Named Instance - I have the following code that worked on a default instance.  Now I am trying to get it to work...

Powershell email throwing error - $File="gci \\abcd\efgh\ijkl | select -last 1" $From = "adcb@gmail.com" $To = "efgh@gmail.com" $Attachment = $File $Subject = "Here's the Email Subject" $Body = "This is what I want to say" $SMTPServer = "smtp.gmail.com" Send-MailMessage...


Data Warehousing : Integration Services

Error: Data conversion failed. The data conversion for column "Produce" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". - I keep getting this error. I have set the value of the "Produce"column to nvarchar MAX but still no luck....

Importing a Flat File Containing Header and Detail Rows - Hello All,  I'm not quite sure how to explain my situation without confusing the living hell out of everyone but i'll...

Error scheduling SSIS packages that were encrypted - Whenever I attempt to create a new job and add a step with a SSIS encrypted (with the Protection Level...


SQL Server 7,2000 : T-SQL

Count Decimal Places - i'm trying to count the number of decimal places in a field. e.g. mynumber decimal 9 (18,9) Len(mynumber) result = 11 I've tried...


Career : Events

The SQL Saturday Thread - As popular as SQL Saturday is, I'm surprised that nobody created a thread dedicated to SQL Saturday, so I created...

This email has been sent to {user_email}. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com