Improving SQL Server 2016 Large Database Performance (SQLServerCentral 6/11/2018)

Laden...

6 jaren geleden

Html
Tekst

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

Featured Contents

Improving SQL Server 2016 Large Database Performance SQL Prompt Safety Net Features for Developers Converting a Database to In-Memory OLTP Query To Retrieve Statistics Data: dm_db_stats_histogram (From the SQLServerCentral Blogs) Spreading Job Run Times Out on an MSX/TSX Scheduled Job (From the SQLServerCentral Blogs)

Featured Script

Statistics not updated for the last 30 days

The Voice of the DBA

Be Prepared with Baselines

I visited a doctor recently, and he told me a measurement he'd made. I asked if it was good or bad, and he said he had no idea. The value varied too much from person to person, and without values from the past, he couldn't really evaluate the significance of it. He will be able to in the future, now that he has a value, but there's nothing that can be done now. At this point, he has a baseline (of sorts) and can now start to judge how things change over time.

After that visit, I started thinking about Page Life Expectancy (PLE). PLE is one of those counters that so many DBAs look at early in their career. Often they've read guidance that they should worry once this is below 300, which isn't true. There are calculations for this, but they are based on your system, and really, they're a rough rule of thumb. Really you need to measure this for your system, so that you know what a the value often is and then worry when it dips.

To do that you need a baseline. You need to measure various metrics about your system over time so that you understand what's a normal value. Plenty of experts, like Erin Stellato and Tim Radney have written about baselines, why they're important, and what you might want to capture. In fact, we have quite a few articles on baselines at SQLServerCentral.

If that sounds like a lot of work to you, I agree. I've built systems in the past that captured metrics on my instances and stored the data. I wrote reports to view data, alerts to let me know when something is breaking (or broken), and maintenance that kept data storage under control. I essentially had to be both the software developer and operations staff for my systems. That works, but I'd try to avoid repeating that effort from now on. As Tim mentions in his piece, there are better ways to do this. There are products, such as SQL Monitor and SQL Sentry, that capture this data for you, that won't have typos, mistakes, or holes in their operation. Some will even show you the baseline visually to see if things are withing expected ranges.

The monitoring software does lots for you, though at a price. It's tested, and it does all the gathering, storage, basic analysis and alerting in a way that allows you to spend time on actually fixing issues, tuning queries, and providing value for your organization. I think it's worth the cost, since I know that my time is better spent on solving problems, not writing monitoring software. You may feel the same way or youj may not. You may prefer to write your own system, or you may not have a budget and be forced to build your own. Whichever route you go, make sure you set up a baseline. You'll appreciate having one the next time your phone rings with a call that the server is slow.

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 ( 3.6MB) podcast or subscribe to the feed at iTunes and Libsyn.

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

Write, format, analyze, and refactor SQL fast with SQL Prompt

Writing SQL is 50% faster with SQL Prompt. Your SQL code can be formatted just the way you like it, you can create and share snippets with your team, and with code analysis you get suggestions to improve your SQL as you type. Download your free trial

How to track every change to your SQL Server database

See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. Learn more

Featured Contents

Improving SQL Server 2016 Large Database Performance

Henrik Thomsen from SQLServerCentral.com

This article aims to save you days or weeks of troubleshooting when using SQL Server 2016 for large databases More »

SQL Prompt Safety Net Features for Developers

Phil Factor describes a few common mishaps that everyone working in SSMS will have experienced a couple of times, and how SQL Prompt “Tab Magic” (now more prosaically called Tab Color and Tab History) provides ‘insurance’ against their consequences. More »

Converting a Database to In-Memory OLTP

Additional Articles from SimpleTalk

In-Memory OLTP was introduced with SQL Server 2014 as a way to improve transaction performance. There are several scenarios that are not supported, such as some data types, and Darko Martinovic describes the issues and provides workarounds. He also has provided a tool to assist in migrating disk-based tables to In-Memory optimized tables. More »

From the SQLServerCentral Blogs - Query To Retrieve Statistics Data: dm_db_stats_histogram

Grant Fritchey from SQLServerCentral Blogs

Starting with SQL Server 2016 Sp1 CU2, a new way of directly querying statistics, specifically the histogram, has been introduced:... More »

From the SQLServerCentral Blogs - Spreading Job Run Times Out on an MSX/TSX Scheduled Job

taboggiano@gmail.com from SQLServerCentral Blogs

Problem My company heavily uses MSX/TSX jobs for everything. But our SAN doesn’t necessarily like if we run all our index... More »

Question of the Day

Today's Question (by Steve Jones):

There is a basic Availability Group (BAG) setup on a server. The main DBA gave me access to the system and I connect with SSMS. I see the Availability Group node in SSMS and can expand the BAG to see my database. I want to change the secondary to read only access, but the checkbox doesn't work. What's the most likely cause?

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: Availability Group (AG).

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

The Phoenix Project: A Novel about IT, DevOps, and Helping Your Business Win

The company's new IT initiative, code named Phoenix Project, is critical to the future of Parts Unlimited, but the project is massively over budget and very late. The CEO wants Bill to report directly to him and fix the mess in ninety days or else Bill's entire department will be outsourced. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

What are the options for automatic tuning in Azure SQL Database (May 2018)?

Answer: FORCE_LAST_GOOD_PLAN, CREATE_INDEX and DROP_INDEX

Explanation:

There are three options for automatic tuning in Azure SQL Database (May 2018). These are CREATE_INDEX, DROP_INDEX, and FORCE_LAST_GOOD_PLAN.

Ref: Enable automatic tuning - click here

» Discuss this question and answer on the forums

Featured Script

Statistics not updated for the last 30 days

Yusuf Kahveci from SQLServerCentral.com

We want to keep them up to date as we know that statistics are vital for our Performance. So I will share with you the script below that you can follow and update our statistics. In the script below you can find the statistics that have not been updated for 30 days and can automate your process accordingly. Yusuf KAHVECI yusufkahveci@sqlturkiye.com www.sqlturkiye.com Thanks.

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

???????????????????????????Q/?501146313??????????????????????/????University of Sunshine Coast - ????????????????????????????????????????????????,?????????,????!????????QQ??501146313?????????????????????????????????????????????????,?????????,????!????????QQ??501146313?????????????????????????????????????????????????,?????????,????!????????QQ??501146313? ?????? ?.???????????????????????????????! ?.??????(?????????,??????) ?.???????????(?????!???????????) ?.?????????(???????

??????????????????????Q/?501146313??????????SMU??????????Saint Mary's University - ??????????????????????Q/?501146313??????????SMU??????????Saint Mary's University??????????????????????Q/?501146313??????????SMU??????????Saint Mary's University??????????????????????Q/?501146313??????????SMU??????????Saint Mary's University ????????????????????????????????????????????????,?????????,????!????????QQ??501146313? ?????? ?.???????????????????????????????! ?.??????(

???????????????????????????Q/?501146313??????????????????????/???? Southern Cross University - ???????????????????????????Q/?501146313??????????????????????/???? Southern Cross University???????????????????????????Q/?501146313??????????????????????/???? Southern Cross University???????????????????????????Q/?501146313??????????????????????/???? Southern Cross University???????????????????????????Q/?501146313??????????????????????/???? Southern Cross University ???????????????????????????

???????????????????????????Q/?501146313??????????????????????/???? Southern Cross University - ????????????????????????????????????????????????,?????????,????!????????QQ??501146313? ?????? ?.???????????????????????????????! ?.??????(?????????,??????) ?.???????????(?????!???????????) ?.?????????(???????,?????????)???????QQ??501146313? ??????????? ????? ????? ????? ??????? ????? ??????? ????? ??????? ???? ???? ???? ?????? ?????? ?????? ???? ?????? ????? ?????? ???

??????????????????????Q/?501146313???????????SFU??????????????Simon Fraser University - ????????????????????????????????????????????????,?????????,????!????????QQ??501146313? ?????? ?.???????????????????????????????! ?.??????(?????????,??????) ?.???????????(?????!???????????) ?.?????????(???????,?????????)???????QQ??501146313? ??????????? ????? ????? ????? ??????? ????? ??????? ????? ??????? ???? ???? ???? ?????? ?????? ?????? ???? ?????? ????? ?????? ???

??????????????????????Q/?501146313???????????SFU??????????????Simon Fraser University - ????????????????????????????????????????????????,?????????,????!????????QQ??501146313? ?????? ?.???????????????????????????????! ?.??????(?????????,??????) ?.???????????(?????!???????????) ?.?????????(???????,?????????) ???????????: ?????,???????????……???????? ????????,??????; ?????????????????; ???????,????; ??????????,???????; ?????????????

???????????????????????????Q/?501146313?????????????????????/???? Northern Territo - ????????????????????????????????????????????????,?????????,????!????????QQ??501146313? ?????? ?.???????????????????????????????! ?.??????(?????????,??????) ?.???????????(?????!???????????) ?.?????????(???????,?????????) ???????????: ?????,???????????……???????? ????????,??????; ?????????????????; ???????,????; ??????????,???????; ?????????????

???????????????????????????Q/?501146313??????????????????????/????Victoria University - ????????????????????????????????????????????????,?????????,????!????????QQ??501146313?????????????????????????????????????????????????,?????????,????!????????QQ??501146313? ?????? ?.???????????????????????????????! ?.??????(?????????,??????) ?.???????????(?????!???????????) ?.?????????(???????,?????????)???????QQ??501146313? ??????????? ????? ????? ????? ??????? ?????

???????????????????????????Q/?501146313??????????????????????/????University of Sunshine Coast - ????????????????????????????????????????????????,?????????,????!????????QQ??501146313? ?????? ?.???????????????????????????????! ?.??????(?????????,??????) ?.???????????(?????!???????????) ?.?????????(???????,?????????) ???????????: ?????,???????????……???????? ????????,??????; ?????????????????; ???????,????; ??????????,???????; ?????????????

???????????????????????????Q/?501146313??????????????????????/???? Southern Cross University - ????????????????????????????????????????????????,?????????,????!????????QQ??501146313? ?????? ?.???????????????????????????????! ?.??????(?????????,??????) ?.???????????(?????!???????????) ?.?????????(???????,?????????) ???????????: ?????,???????????……???????? ????????,??????; ?????????????????; ???????,????; ??????????,???????; ?????????????

???????????????????????????Q/?501146313???????????????????????/????Edith Cowan University - ????????????????????????????????????????????????,?????????,????!????????QQ??501146313? ?????? ?.???????????????????????????????! ?.??????(?????????,??????) ?.???????????(?????!???????????) ?.?????????(???????,?????????) ???????????: ?????,???????????……???????? ????????,??????; ?????????????????; ???????,????; ??????????,???????; ?????????????

???????????????????Q/?501146313?????????????????????????????The Ohio State University,Columbus - ????????????????????????????????????????????????,?????????,????!????????QQ??501146313? ?????? ?.???????????????????????????????! ?.??????(?????????,??????) ?.???????????(?????!???????????) ?.?????????(???????,?????????) ???????????: ?????,???????????……???????? ????????,??????; ?????????????????; ???????,????; ??????????,???????; ?????????????

?????????????????Q/?501146313????????????????????The London School of Economics and Political Science - ?????????????????Q/?501146313????????????????????The London School of Economics and Political Science?????????????????Q/?501146313????????????????????The London School of Economics and Political Science?????????????????Q/?501146313????????????????????The London School of Economics and...

SQL Server 2017 : SQL Server 2017 - Development

Get Index Fragmentation Cheaply - Is there a cheaper way to write this (from a query cost perspective)? It seems to churn quite a bit...

SQL Server 2016 : SQL Server 2016 - Administration

?????????????????Q/?501146313????????????UG??????????University of Glamorgan - ??????????,??????Q /?501146313????????????UG??????????University of Glamorgan ??????????,??????Q /?501146313????????????UG????????????????

SQL Server 2012 : SQL 2012 - General

T-SQL help needed to eliminate duplicates from table - To make a long story short, one of our tables that have rx data has got messed up because the...

SQL Server 2008 : SQL Server 2008 - General

Error - SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. - Hi Below statement is giving error insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;', 'SELECT * FROM ') select * FROM ... Is it possible thru above statement...

Export to Excel - Hi I want Data from the Sql table to Excel in predefined Excel Template . I have attached file and this should...

Affinity Mask... - Hey Folks, Appreciate if somebody can share knowledge on factors one has to take into account before setting processor affinity mask for SQL instances....

This email has been sent to newsletter@newslettercollector.com. 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.

Deel deze op

Laden...

Andere nieuwsbrieven van Sqlservercentral.com

A Trigger... just not that kind Sqlservercentral.com
8 uren geleden
Configuring Maintenance Plans in SQL Server (20 24-02-21) Sqlservercentral.com
Afgelopen vrijdag om 08:17
How to Resolve SQL Server Database Stuck in Rec overy Pending State (2025-02-19) Sqlservercentral.com
Afgelopen woensdag om 08:13

Meer nieuwsbrieven van Sqlservercentral.com

Laden...

Improving SQL Server 2016 Large Database Performance (SQLServerCentral 6/11/2018)

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

Featured Contents

Featured Script

The Voice of the DBA

Be Prepared with Baselines

The Voice of the DBA Podcast

Featured Contents

Improving SQL Server 2016 Large Database Performance

SQL Prompt Safety Net Features for Developers

Converting a Database to In-Memory OLTP

From the SQLServerCentral Blogs - Query To Retrieve Statistics Data: dm_db_stats_histogram

From the SQLServerCentral Blogs - Spreading Job Run Times Out on an MSX/TSX Scheduled Job

Question of the Day

Today's Question (by Steve Jones):

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

The Phoenix Project: A Novel about IT, DevOps, and Helping Your Business Win

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

Featured Script

Statistics not updated for the last 30 days

Database Pros Who Need Your Help

SQL Server 2017 : SQL Server 2017 - Administration

SQL Server 2017 : SQL Server 2017 - Development

SQL Server 2016 : SQL Server 2016 - Administration

SQL Server 2012 : SQL 2012 - General

SQL Server 2008 : SQL Server 2008 - General

Deel deze op

Andere nieuwsbrieven van Sqlservercentral.com

Gerelateerde nieuwsbrieven