Contenido

System Databases ............................................................................................................................................................ 2

Difference Between Clustered And Non-Clustered Indexes .............................................................................................. 2
Disadvantages Of Clustered Index ................................................................................................................................ 3
TempDB ........................................................................................................................................................................... 3
Features New versions of SQL Server ............................................................................................................................... 4
SQL 2014 ...................................................................................................................................................................... 4
AlwaysOn Enhancements ......................................................................................................................................... 4
Buffer Pool Extension ............................................................................................................................................... 4
Backup Encryption.................................................................................................................................................... 4
SQL 2016 ...................................................................................................................................................................... 4
Enhancements to AlwaysOn ..................................................................................................................................... 4
TempDB ....................................................................................................................................................................... 4
The Query Store ....................................................................................................................................................... 4
Always Encrypted ..................................................................................................................................................... 4
Row Level Security ................................................................................................................................................... 5
SQL 2017 ...................................................................................................................................................................... 5
SQL 2019 ...................................................................................................................................................................... 5
Always On Availability Groups .................................................................................................................................. 5
Installation ............................................................................................................................................................... 5
Difference between a covered query and a covering index............................................................................................... 5
A covered query ........................................................................................................................................................... 6
A covering index........................................................................................................................................................... 6
Deadlocks ........................................................................................................................................................................ 6
How can I capture more information about it? ............................................................................................................. 6
Use trace flags to write to the log ............................................................................................................................. 6
Capture a deadlock graph with Profiler or Extended Events.......................................................................................... 7
Profiler ..................................................................................................................................................................... 7
Cluster qurum .................................................................................................................................................................. 8
Query performance ......................................................................................................................................................... 8
System Databases
SQL Server comes with the following system databases:
- master Database: Records all the system-level information for an instance of SQL Server.

- msdb Database: Is used by SQL Server Agent for scheduling alerts and jobs.

- model Database: Is used as the template for all databases created on the instance of SQL Server. Modifications made
to the model database, such as database size, collation, recovery model, and other database options, are applied to any
databases created afterward.

- Resource Database: Is a read-only database that contains system objects that are included with SQL Server. System
objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

- tempdb Database: Is a workspace for holding temporary objects or intermediate result sets.

Difference Between Clustered And Non-Clustered Indexes

A clustered index stores and sort the rows in the table based on their key values. These key values are stored in a B-tree
structure that helps the SQL Server to search the rows associated with the key values efficiently and quickly.

DETERMINES HOW A TABLE IS WRITTEN TO DISK

ONLY ONE PER TABLE
AUTOMATICALLY CREATED WITH PRIMARY KEY

A non-clustered index has a separate structure from the actual data rows of the table. A non-clustered index has the
index key values and each key-value entry contains a pointer to the row that has the key value. It is just like a “table of
content” of a book.

The use of the non-clustered index in SQL server is a better choice than clustered index because clustered index stores
and sort data in the physical order and a table can have only one clustered index. On the other hand, non-clustered
index stores data in logical order and a table can have multiple non-clustered indexes. Therefore, in this article, we are
going to discuss “When to use the non-clustered index in SQL server”.
Disadvantages Of Clustered Index
The clustered index takes a long time to update records when the fields in the clustered index are changed.

Clustered index sorts all data rows physically. Therefore, it takes a long time to search for the required data.

The leaf nodes contain data pages in the clustered index.

A table contains only one clustered index.

Insert and update operations take a huge amount of time in the clustered index.

When to use the non-clustered index in SQL server

The users can create a non-clustered index in SQL server instead of the clustered index in various conditions. Some of
them are listed as below:

Large Index Key Size: When the size of the index key is large then create a non-clustered index on the table.

Retrieve Data Quickly: To retrieve data from the table quickly use the non-clustered index in the SQL server.

Avoid Overhead Cost: To avoid the overhead cost associated with clustered index creates a non-clustered index on the
table.

Avoid Physical Sorting: A non-clustered index stores data in logical order and does not sort data rows physically.

For Fast Insert And Update: The insert and update operation is faster in the non-clustered index.

To Create Multiple Index: A table can have multiple non-clustered indexes in SQL server. Therefore, it can be used to
create more than one index.

Conclusion

Non-cluster index stores data in logical order and does not sort data rows physically. It retrieves data quickly from the
table. Therefore, we can use non-cluster to retrieve data quickly, to create more than one index, to avoid overhead cost,
to prevent the physical sorting of data rows and when the index key size is large.

TempDB

In this example, the query runs for 3 minutes before we hit the 200MB file size limit, as shown in Figure 25, and get an
error that the filegroup is full.

At this point the query fails, obviously, as will any other queries that need to use TempDB. SQL Server is still functioning
properly, but as long as the temp table #HoldAll exists, TempDB will stay filled.

Your three options, as a DBA, are to:

Restart SQL Server.

Try to shrink the TempDB database.

Find the errant query and eradicate it.

Features New versions of SQL Server

SQL 2014
AlwaysOn Enhancements
Microsoft has enhanced AlwaysOn integration by expanding the maximum number of secondary replicas from four to
eight. Readable secondary replicas are now also available for read workloads, even when the primary replica is
unavailable. In addition, SQL Server 2014 provides the new Add Azure Replica Wizard, which helps you create
asynchronous secondary replicas in Windows Azure.

Buffer Pool Extension

SQL Server 2014 provides a new solid state disk (SSD) integration capability that lets you use SSDs to expand the SQL
Server 2014 Buffer Pool as nonvolatile RAM (NvRAM).

Backup Encryption
One welcome addition to SQL Server 2014 is the ability to encrypt database backups for at-rest data protection. SQL
Server 2014 supports several encryption algorithms, including Advanced Encryption Standard (AES) 128, AES 192, AES
256, and Triple DES. You must use a certificate or an asymmetric key to perform encryption for SQL Server 2014 backup

SQL 2016

Enhancements to AlwaysOn
The SQL 2016 version improves the ability of the AlwaysOn feature to have around three synchronous replicas in order
to support availability and disaster recovery functions. It also relies on Distributed Transaction Coordinator support for
load balancing and manages automatic failover.

Always On failover cluster instances: 16

Up to 8 secondary replicas, including 2 synchronous secondary replicas

TempDB

TempDB has been improved with performance in mind. You no longer have to determine how many TempDB files you
have to add for yourself. Instead SQL server 2016 decides on a default to use by taking into account the number of
logical processors on the server.

The Query Store

The Query Store is a completely new feature which will help DBA’s to troubleshoot performance issues resulting from
changes in the query plan. Historical data is captured to allow for comparisons to be made between queries, plans and
statistics. They Query Store is not enabled by default, so in order to use it you can enable it with this command:

Always Encrypted

With Always Encrypted, the data is, well… always encrypted. The encryption takes place on the client side when it is
written, and requires a special driver and an encryption key in order to be able to read the encrypted data.
Row Level Security

Row level security allows you to restrict the rows that a user is able to see, allowing them only to see rows which are
relevant to their function. Row level security is implemented by creating inline table functions. This allows a lot of
flexibility in terms of what criteria could be used to determine a user’s access but could also have a performance impact
if not handled with care.

SQL 2017

SQL Server on Linux.

Resumable online index rebuild.
SQL Server machine learning services.
Query processing improvements.
Automatic database tuning.
TempDB file size improvements.
Smart differential backup.
Smart transaction log backup.

SQL 2019

Always On Availability Groups

For the first time, Always On Availability Groups were introduced in SQL Server 2012. Since then, Microsoft has made
some improvements to this feature in each new release. In the same way, in SQL Server 2019, has made improvements
to the high availability and disaster recovery feature. In the new version, the Always On Availability Group can have 5
synchronous replicas (1 primary copy and 4 secondary ones) for failover purposes, whereas there were 3 limitations in
previous SQL Server 2017.

One of the great things about this feature is that it enables numerous duplicates of a database to be reproduced on
different servers. SQL Server 2019 can redirect connections for customer applications from a secondary replica to the
primary one. This means that a customer can be redirected to the primary replica without using the accessibility group
listener, which is a virtual system name used to interface customers to databases in replicas

Installation

The new preview provides three types of installation options – Basic, Custom and Download Media. Basic installation
requires less disk space, as these are the files needed to run that basic install, while a custom install brings down the
whole SQL Server 2019 installation media. Basic installation is sufficient for most basic developer tasks. I would
recommend a custom installation for the full evaluation and Download Media for installing on more than one machine
to evaluate the SQL Server’s cluster features.

Difference between a covered query and a covering index.

A covering index is one which can satisfy all requested columns in a query without performing a further lookup into the
clustered index.
Covered queries and covering indexes are different, yet closely related. A query is covered if all the columns it uses come
from one or more indexes. These columns include the columns you want the query to return as well as columns in any
JOIN, WHERE, HAVING, and ORDER BY clause.

A covered query typically is considered advantageous because data access through indexes can be more efficient.
However, the high-speed access that this kind of query facilitates can become costly when you update the table because
you must maintain the indexes.

A covering index—which is used in covered queries—can provide some or all of the indexed columns that a covered
query uses. If a covering index is also a composite index (i.e., it indexes more than one column in its base table or view),
it might contain columns that aren't used in the covered query but are used instead in other queries that have
overlapping columns.

Deadlocks
When a deadlock occurs in SQL Server, two or more tasks are running and holding locks on data. Then, each task
requests to lock the data the other task is already holding. Both tasks wait for the other to give up.

How do I know if it’s happening to me?

You might notice slowness in your application. Users might complain about slowness or error messages. And, you’ll see a
message in the SQL Server Log that says “Transaction (Process ID 103) was deadlocked on resources with another
process and has been chosen as the deadlock victim.”

Your server has encountered a deadlock.

How can I capture more information about it?

You have several options: you can enable a trace flag to write more information to the log, you can capture deadlocks
graphs using Profiler or Extended Events, and you can track the number of deadlocks occurring using Performance
Monitor.

Use trace flags to write to the log

Two trace flags can be enabled to capture more information in the log: 1204 and 1222. 1204 lists the information by
node; 1222 lists it by process and resource. You can enable both simultaneously. To enable the flags, use the command

DBCC TRACEON (1204, 1222)

DBCC TRACEON (1204, 1222)

Here’s a small sample of what would appear in the log:

Capture a deadlock graph with Profiler or Extended Events

It is – you just have to know where to look to for a deadlock graph. In SQL Server 2005, 2008, and 2008R2, I prefer to use
a Profiler trace; in SQL Server 2012 and 2014 I rely on Extended Events.

Profiler

When you set up a Profiler session to gather deadlock information, select the “Deadlock graph”, “Lock:Deadlock” and
“Lock:Deadlock Chain” events. Make sure you go to the Events Extraction Settings tab and select the option “Save
Deadlock XML events separately”. This ensures that the .xdl files – the graphs – will be saved as separate files. When you
start the session, if a deadlock occurs, you will see “Deadlock graph” captured as an event.

The task with the blue X on it is the victim. By holding your mouse over the process oval, you can see what statement
was being executed by that task. The Key Lock rectangles will help you find the object and index that the locking and
blocking occurred on.

Extended Events

In the future, Profiler will be removed from SQL Server. Extended Events (EE) is taking its place. You have two options for
getting deadlock information using EE. First, there is the system_health session that, by default, runs continuously. You
can mine this for deadlocks that have happened in the past. You can also set up your own session to capture only
deadlock information (and any other relevant events).

If using the system_health session, filter on “xml_deadlock_report”. If setting up your own session, capture the same
event. If you are using the GUI available in 2012 and 2014, you can open the file and view the deadlock graph on the
“Deadlock” tab.
If parsing the results using T-SQL and XQuery, you would use the .query method to extract the deadlock XML, like such:

SELECT DeadlockEventXML.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS deadlock_graph

SELECT DeadlockEventXML.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS deadlock_graph

This generates the XML, which you would then save as an .xdl, then close and re-open with SSMS to view the graph

https://www.mssqltips.com/sqlservertip/5658/capturing-sql-server-deadlocks-using-extended-events/

Cluster qurum
If your quorum disk goes down, the cluster will stay up but you will receive warnings in the cluster log.

The quorum disk acts as a vote for clusters with an even number of votes. In order for a cluster to stay up, a "Quorum
needs to be achieved". This "Quorum" is dependent on how many nodes there are in the cluster as well as Quorum
disk/file share witness and settings you might have changed.

If you have a 2 node cluster with a quorum disk, if the quorum disk goes down along with one of the nodes, the cluster
will go down completely.

For a cluster with 3 nodes, the cluster can sustain 1 node failure

For a cluster with 5 nodes, the cluster can sustain 2 node failures

Etc.........
Start-ClusterNode –Name "WS-CLUSTER1" –FixQuorum

Query performance
There are several basic ways to improve Execution Plan:

• Productive indexes
• Optimal table joining order
• Hints for SQL query optimizer
• Statistics

• Don’t use the * in your queries. A SELECT * does an overload on the table, I/O and network bandwidth.
• All columns involved in indexes should appear on WHERE and JOIN clauses on the same sequence they appear
on index.
• Avoid VIEWs. Use them only when there are benefits of doing so. Do not abuse them.
• Verify if a critical query gains performance by turning it in a stored procedure.
• Avoid too much JOINs on your query: use only what is necessary!
• Avoid cursors at all costs!
• Always restrict the number of rows and columns of your result. That way, you save disk, memory and network of
the database server. Always verify your WHERE clause and use TOP if necessary.
• Verify if your server isn’t suffering from not-enough-disk-space illness. Some times you lose time searching for all
kind of problems only to find out that the server’s disk are almost full a few hours later. Always reserve at least 30% of
available space on your disc.
• SQL Server is case insensitive: he does not care about ‘A’ ou ‘a’. Save time and don’t use functions like LOWER
and UPPER when comparing VARCHARs. (As pointed out by Aravind V Shibu, this depends on the collation of your server
or table. Please check it before!)
• The decreasing performance order of operators is: = (faster)>, >=, <, <=, LIKE, <> (slower)
• If a query is slow and your index is not being used by it (remember to check your execution plan), you can force
it using WITH(INDEX=index_name), right after the table declaration on the FROM clause.
• Use EXISTS or NOT EXISTS instead of IN or NOT IN. IN operators creates a overload on database.
• Try to use BETWEEN instead of IN, too.
• When using LIKE operator, try to leave the wildcards on the right side of the VARCHAR.
• Always avoid to use functions on your queries. SUBSTRING is your enemy. Try to use LIKE instead.
• Queries with all operations on the WHERE clause connected by ANDs are processed from the left to right. So, if a
operation returns false, all other operations in the right side of it are ignored, because they can’t change the AND result
anyway. It is better then to start your WHERE clause with the operations that returns false most of the time.
• Sometimes is better to make various queries with UNION ALL than a unique query with too much OR operations
on WHERE clause. Test it.
• When there is a HAVING clause, it is better to filter most results on the WHERE clause and use HAVING only for
what it is necessary.
• If there is a need of returning some data fast, even if it is not the whole result, use the FAST option.
• Use, if possible, UNION ALL instead of UNION. The second eliminates all redundant rows and requires more
server’s resources.
• Use less subqueries. If you must use it, try to nest all of them on a unique block.
• Avoid to do much operations on your WHERE clause. If you are searching for a + 2 > 7, use a > 5 instead.
• Use more variable tables and less temporary tables.
• Use functions to reuse code. But don’t exaggerate on using them!
• To delete all rows on a table, use TRUNCATE TABLE statement instead of DELETE.
• If you have a IDENTITY primary key and do dozens of simultaneous insertions on in, make it a non-clusterized
primary key index to avoid bottlenecks.

• Now, some tips for the table structure. Sometimes it is necessary to make some alterations on the table design
to extract more performance!

• All tables should have a primary key. Except data warehouses and the like sometimes.
• All tables should have a clusterized index, normally on the primary key.
• Don’t be afraid to create non-clusterized indexes on most tables. Just be sure that you aren’t overindexing your
tables! Too much indexes degrades insertions.
• If you are creating an index, check the queries that are made against the table. Give preference to index columns
that appear on most WHERE and JOIN clauses, and their order!
• Avoid TRIGGERs. Use it only as a last resource. It is better to use CONSTRAINTs and STORED PROCEDUREs to
maintain the integrity of your databases!