Optimization

Optimization & Performance Tools

SQL 

Tools to Monitor events in SQL Server and for tuning the physical database design.

Point to maximize throughput, yielding optimal performance. Efficient network traffic, disk I/O, and CPU usage are key to peak performance.

 

MicrosoftTools

Additional Tooling;

    Plan Explorer Installation & Overview

        Plan Explorer is a FREE lightweight .NET-based tool that builds upon and supplements the capabilities of SSMS for execution plan analysis. 

Additional Considerations

Data Types:

Conclusion on Data Types

•        Logical Disk performance – Saving 500GB of with around 500 billion rows of using tinyint vs int – faster disk processing

•         For Testing and fabricating over a million rows - could have benefited from more permutations. Conclusively reflects bigger impact are on space occupied by the non-clustered index.

•         Improved I/O performance - more records fit in memory, thus faster data modification process will be - not wasting space in the SQL Server buffer cache.

•         Index Performance - using the smallest yet appropriate data type for your columns, the storage requirement for your indexes will also be smaller.

•         Furthermore Rows and Index entries are stored in 8k pages. So with larger Databases we affect the number of rows per page - page density – think page to row ratio.

•         Conclusively Database design matters!

•         DataType Considerations;

o         E.g. BIGINT occupies 8 bytes of space, while a TINYINT occupies 1 byte

o         The big culprits are usually NVARCHAR versus VARCHAR and INT vs. TINYINT.

o         Use char when the sizes of the column data entries are consistent.

o         Use varchar when the sizes of the column data entries vary considerably.

o         Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.

o         Primary keys should always be unsigned

o         String data types for string Values

o         Number data types for numbers

 

Select *

          Why not;

•        Performance; Not all fields are indexed, forcing the system to do a full table which is less efficient

•        Indexing Issues; Performance are based on more expensive methods, assume column added the optimizer will ignore the optimized covering index and performance would drop substantially.

•        Data in motion; retrieving more columns than your application needs to function – from database server to client, thus increasing unnecessary load on machines together with travel time across the network – simply returning more data than required.

•        Views; Binding problems can be created, especially when two columns are named the same

•        Could also create an error waiting to happen as a column data type, or name have been altered or column order changed.

•        It is anti-pattern; does not specify the purpose of the query, columns used by application is opaque or cloudy.

•        Consider Estimated execution plan in following example;

SELECT * FROM sys.check_constraints

--vs.

SELECT name,definition, uses_database_collation FROM sys.check_constraints  order by name

 

 

Set Based Queries

          Query in Sets

o        T-SQL is a declarative set based programing language.

o        SQL language is optimized for set-based solutions rather than procedural solutions

o        Developers unfamiliar with declarative queries turn towards looping mechanisms, such as cursors, which retrieve data one row at a time resulting in query inefficiencies.

o        Cursor logic results in performance inefficiencies (on more than a few rows. However as sets of data grow we will experience troublesome performance issues)

 

 Periodic Tasks

Daily

•        Backups

o        Large Databases – either differential (before major changes), file or filegroup backups with full Backup Plan.

o        Before differential make sure we have full copy of database backups before other backups

•        SQL Server Error log

o        Review the SQL Server error log for any errors or security issues (successful or failed logins) that are unexpected.

o        By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files.

o        New error log files are generated/ created each time an instance of SQL Server is started

--ReturnCurrent error-log information

EXEC master..sp_enumerrorlogs

--Read Error log - INlc n - number of page

sp_readerrorlog 1

•        Windows Event Log

o        Event Viewer – Windows Logs – Application and

       Application and Service logs

o        Pay attention to MSSQLSERVER  or Named Instances MSSQL$<Instance>

o        To view the log of a different computer, right-click Event Viewer (local), select Connect to another computer, and complete the Select Computer dialog box.

•        SQL Server Agent  - Review for failed SQL Server Agent Jobs

o        SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks, which are called jobs in SQL Server 2016.

o        Run a job on a schedule, in response to a specific event, or on demand.

       Create a schedule:

-- creates a schedule named RunOnce.  

-- The schedule runs one time, at 23:30 on the day that the schedule is created. 

USE msdb ;

GO

 

EXEC dbo.sp_add_schedule

    @schedule_name = N'RunOnce',

    @freq_type = 1,

    @active_start_time = 233000 ;

 GO

 

-- Schedule a job;

USE msdb ;

GO

-- creates a schedule named NightlyJobs. 

-- Jobs that use this schedule execute every day when the time on the server is 01:00. 

 

EXEC sp_add_schedule

   @schedule_name = N'NightlyJobs' ,

    @freq_type = 4,

    @freq_interval = 1,

    @active_start_time = 010000 ;

GO

 

-- attaches the schedule to the job BackupDatabase

EXEC sp_attach_schedule

   @job_name = N'BackupDatabase',

   @schedule_name = N'NightlyJobs' ;

GO

•        Check HA or DR Log - High Availability and Disaster Recovery for SQL Server – be it local, or VM’s

o        Check Logs

o        Log Shipping (at dB level)

o        Mirroring (will be removed ni future versions), Clustering, replication etc.

o        For data protection through SQL Server, use Always On availability group - local high availability through redundancy at the server-instance level—a failover cluster instance (FCI)

o        Use Always On Failover Cluster Instances on SAN (Shared disk solutions)

•        Review Performance logs

Most of the time, IN and EXISTS give you the same results with the same performance.

On the other hand, when you use JOINS you might not get the same result set as in the IN and the EXISTS clauses.

Topic	Task
Monitor SQL Server Components	Required steps to monitor any SQL Server component, such as Activity Monitor, Extended Events, and Dynamic Management Views and Functions, etc.
Performance Monitoring and Tuning Tools	Lists the monitoring and tuning tools available with SQL Server, such as Live Query Statistics, and the Database Engine Tuning Advisor.
Upgrading Databases by using the Query Tuning Assistant	Keep workload performance stability during the upgrade to newer database compatibility level.
Monitoring Performance by Using the Query Store	Use Query Store to automatically capture a history of queries, plans, and runtime statistics, and retain these for your review.
Establish a Performance Baseline	How to establish a performance baseline.
Isolate Performance Problems	Isolate database performance problems.
Identify Bottlenecks	Monitor and track server performance to identify bottlenecks.
Use DMVs to Determine Usage Statistics and Performance of Views	Covers methodology and scripts used to get information about the performance of queries.
Server Performance and Activity Monitoring	Use SQL Server and Windows performance and activity monitoring tools.
Monitor Resource Usage	Using System Monitor (also known as perfmon) to measure the performance of SQL Server using performance counters.