Basics of Filtered Indexes and Filtered Statistics in SQL Server

SQLServerF1

Starting with SQL Server 2008, Microsoft has introduced Filtered Indexes and Filtered Statistics, where in you can provide a where condition while creating the index or statistics, thus limiting the amount of rows in an index which is useful is some cases where only a subset of the data has to be read from, which greatly improves the performance.

What are Filtered Indexes?
A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes. A filtered index provides Improved query performance and plan quality, Reduced index maintenance costs, Reduced index storage costs.

What are Filtered Statistics in SQL Server?
SQL Server optimizer uses statistics to create suitable execution plan. So, it is important to keep the statistics up to date for in SQL Server database. SQL Server automatically manages the statistics, but sometime DBAs has to setup jobs to perform statistics update for better performance when there are frequent changes to the data in the tables. Starting with SQL Server 2008, Microsoft has introduced filtered statistics to further benefit the performance of the queries against large tables. Below are some of the basics of the Filtered Statistics in SQL Server.

As per official Microsoft documentation, “Filtered statistics can improve query performance for queries that select from well-defined subsets of data. Filtered statistics use a filter predicate in the WHERE clause to select the subset of data that is included in the statistics.”

It is very easy to create the filtered indexes or statistics which can be create using Create Index or Create Statistics and specifying a where clause with a condition that limits the number of rows matching the requirement. Once the index or statistic is created, SQL Server can use it to satisfy the query thus performing better.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Basics of SQL Server Policy Based Management

SQLServerF1

Policy Based Management is a framework for dealing with one or more occasions of SQL Server. At the point when SQL Server arrangement heads use Policy-Based Management, they utilize SQL Server Management Studio to make approaches to oversee elements on the server, for example, the occurrence of SQL Server, databases, or other SQL Server objects. Policy Based Management is useful in determining the issues introduced in the accompanying situations where An organization approach disallows empowering Database Mail or SQL Mail. A strategy is made to check the server condition of those two highlights. An overseer contrasts the server state with the approach. In the event that the server state is out of agree-ability, the overseer picks the Configure mode and the arrangement brings the server state into consistence.

The AdventureWorks2012 database has a naming tradition that obliges every put away method to begin with the letters AW_. A strategy is made to implement this approach. A director tests this approach and gets a run-down of put away methodology that are out of consistence. In the event that future put away systems don’t agree to this naming tradition, the creation articulations for the put away methodology come up short.

Important Policy Based Management Terms:
Policy Based Management oversaw target – Elements that are overseen by Policy-Based Management, for example, an occasion of the SQL Server Database Engine, a database, a table, or a record. All focuses in a server occasion shape a target order. A target set is the situated of focuses on that outcomes from applying an arrangement of target channels to the target order, for instance, all the tables in the database possessed by the HumanResources blueprint.
Policy Based Management feature – An arrangement of coherent properties that model the conduct or attributes for specific sorts of oversaw targets. The number and attributes of the properties are incorporated with the aspect and can be included or uprooted by just the producer of the feature. A target sort can actualize one or more administration features, and an administration feature can be actualized by one or more target sorts. A few properties of a feature can just apply to a particular variant..
Policy Based Management condition – A Boolean interpretation that indicates an arrangement of permitted conditions of a Policy-Based Management oversaw focus as to an administration aspect. SQL Server tries to watch gatherings when assessing a condition. At the point when SQL Server gatherings don’t precisely coordinate Windows examinations, test your condition to decide how the calculation determines clashes.
Strategy Based Management approach – A Policy-Based Management condition and the normal conduct, for instance, assessment mode, target channels, and calendar. A strategy can contain stand out condition. Strategies can be empowered or debilitated. Approaches are put away in the msdb database.
Policy Based Management approach classification – A client characterized classification to help oversee strategies. Clients can characterize approaches into diverse strategy classes. An approach has a place with unrivaled one strategy class. Arrangement classes apply to databases and servers. At the database level, the accompanying conditions apply

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

High Performance CPU Power Plan for SQL Server for Better Performance

SQLServerF1

It is common to determine and implement best practices while setting up new servers or while trying to identify and fix performance problems with SQL Server running on Windows Server 2008 R2 or higher. Some of these practices are effective and are meant to upgrade the hardware of the server if the server is not performing well as expected. However there are may tips and tricks which can be followed to improve the performance of the server instead of just upgrading the hardware to improve the performance. One of the common issues we generally see is high CPU usage on server running SQL Server instance.

On Windows Server 2008 or more, set the “High Performance” power plan in Control Panel from Power Options. Of course, Windows Server sets the “Adjusted” force arrangement, which empowers vitality protection by scaling the processor execution in view of current CPU use. From Intel X5500 and other last-era CPUs, the clock is throttled down to spare force (Processor P-state), and just increments when CPU use achieves a certain point. The Minimum and Maximum Processor Performance State parameters are communicated as a rate of greatest processor recurrence, with a quality in the reach 0 – 100.

In the event that a server obliges ultra-low inertness, invariant CPU recurrence, or the extremely most astounding execution levels, for example, a database servers like SQL Server, it may not be useful that the processors continue changing to lower-execution states. In that capacity, the High Performance force arrangement tops the base processor execution state at 100 percentage.

Also, it is important to keep in mind that changing this setting alone from control panel does not help, as some servers have this enabled at BIOS level, thus need to make the change at BIOS level too if enabled in BIOS. Also for VM systems, it is important to enable this at guest and host level and on BIOS on host server. Although this is a simple change, but shows greater improvement in performance of busy servers running SQL Server.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Timeout Expired Error When Publishing to SQL Database From SSDT

SQLServerF1

You may received below error when trying to publish the database with SQL Server Data Tools (SSDT) in Visual Studio after you migrating the SQL Server Database to Basic edition.
Failed to import target model DBName, Unable to reconnect to database
Timeout Expired. The timeout period expired prior to completion of the operation or the server is not responding.

This error occurs because the Publish or Generate Script operation high the CPU limit of Basic edition and finally times out. If you check the resource stats you may notice 100% CPU usage during this operation.

You can try couple of methods to get out of this issue, one of them is to upgrade to Standard edition and then retry the operation or try to increase the query timeout value for SSDT, so that the operation completes without timing out. In the present adaptation of SSDT there is no client interface that permits altering the question timeout. Rather the inquiry timeout for SSDT is controlled by the accompanying registry keys

QueryTimeoutSeconds – Default worth is 60 seconds. This controls the measure of time that SSDT will sit tight for a reaction to a standard (non-long running) inquiry. Setting this worth to 0 intends to sit tight inconclusively for a reaction from the server. If you don’t mind take a stab at expanding this quality to 600 and retrying the operation.
LockTimeoutSeconds – Default quality is 5 seconds. This controls the measure of time that SSDT will sit tight for a bolted asset before timeout. Dissimilar to alternate keys, setting this key to 0 intends to surrender quickly when any bolted asset is experienced. Kindly don’t set this quality to 0. In the event that you think that the timeout issue may be brought on by lock discord, expand this timeout and retry the operation.
LongRunningQueryTimeoutSeconds – Default worth is 0. This controls the measure of time that SSDT will sit tight for a reaction to a long running inquiry. An estimation of 0 intends to sit tight uncertainly for a reaction from the server. If its not too much trouble leave this timeout at the default estimation

You can check the “HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\12.0\SQLDB\Database” registry key and change the timeout value accordingly.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

List of All Redgate SQL Server Related Tools or Products

SQLServerF1

There are various tools or products of Redgate developed or brought from other company’s which are related to SQL Server or useful for various tasks for DBAs, Developers, Consultants, Management etc. Depending on the kind of tasks the product or software can be used for, the products are categorized accordingly. Below are some of the Redgate SQL Server Related Tools or Products available.

Redgate Database Lifecycle Management Products: More and more companies are recognizing that agile practices such as continuous integration and continuous delivery are as applicable to databases as they are to applications. Implemented correctly, they reduce risk, improve efficiency, and deliver value to customers sooner.
Quite simply, they enable organizations to proactively manage the processes involved in the application and database lifecycle, including planning, development, release, and maintenance. Some of the softwares available under this category include Database Lifecycle Management, SQL Developer Suite, DLM Automation Suite, DLM Dashboard (FREE), SQL Source Control.

Redgate Development Products: These tools are primarily useful for SQL Server or other products developers used for designing, developing and building projects. Some of the useful tools under this category include SQL Compare, SQL Data Compare, SQL Source Control, SQL Search (FREE), SQL Prompt, SQL Doc, SQL Data Generator, SQL Dependency Tracker, SQL Comparison SDK, SQL Test, SQL Lock (BETA), SQL Developer Suite, SQL Toolbelt.

Redgate Database Administration Products: These tools are primarily useful for SQL Server or other products developers used for managing or administering SQL Server instances and databases. Some of the useful tools under this category include SQL Monitor, SQL Backup Pro, SQL Multi Script, SQL Index Manager, SQL Scripts Manager (FREE), SQL DBA Bundle.

Redgate .NET Products: Some of the useful tools under this category include .NET Developer Bundle, ANTS Performance Profiler, ANTS Memory Profiler, .NET Reflector, SmartAssembly, PInvoke .NET (FREE).

Redgate Oracle Products: Some of the useful tools under this category include Schema Compare for Oracle, Data Compare for Oracle, Source Control for Oracle, Deployment Suite for Oracle, DLM Automation Suite for Oracle.

Redgate MySQL Products: Some of the useful tools under this category include MySQL Comparison Bundle, MySQL Compare, MySQL Data Compare.

Redgate Azure Products: Some of the useful tools under this category include Azure Management Studio, Azure Explorer (FREE), Redgate Cloud Services.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Basics of SQL Server Database Instant File Initialization

SQLServerF1

Data and log files are initialized to overwrite any existing data left on the disk from previously deleted files. Data and log files are first initialized by filling the files with zeros when you perform one of the operations like Create a database, Add files, log or data, to an existing database, Increase the size of an existing file (including autogrow operations), Restore a database or filegroup. File initialization causes these operations to take longer. However, when data is written to the files for the first time, the operating system does not have to fill the files with zeros.

In SQL Server, data files can be initialized instantaneously. This allows for fast execution of the previously mentioned file operations. Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. Log files cannot be initialized instantaneously.

How to Enable Database Instant File Initialization in SQL Server?
Instant file initialization is only available if the SQL Server (MSSQLSERVER) service account has been granted SE_MANAGE_VOLUME_NAME. Members of the Windows Administrator group have this right and can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy. Important point to note regarding instant file initialization is that Instant file initialization is not available when TDE is enabled.

To grant an account the Perform volume maintenance tasks permission:
On the computer where the backup file will be created, open the Local Security Policy application (secpol.msc).
In the left pane, expand Local Policies, and then click User Rights Assignment.
In the right pane, double-click Perform volume maintenance tasks.
Click Add User or Group and add any user accounts that are used for backups.
Click Apply, and then close all Local Security Policy dialog boxes.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Basics of Dedicated Administrator Connection (DAC) in SQL Server

SQLServerF1

What is Dedicated Administrator Connection (DAC) in SQL Server?
Microsoft SQL Server gives a Dedicated Admin Connection (DAC). The DAC permits a head to get to a running case of SQL Server Database Engine to investigate issues on the server—notwithstanding when the server is lethargic to other customer associations. The DAC is accessible through the sqlcmd utility and SQL Server Management Studio. The association is just permitted from a customer running on the server. No system associations are allowed.

To utilize SQL Server Management Studio with the DAC, unite with a case of the SQL Server Database Engine with Query Editor by writing ADMIN: before the server name. Object Explorer can’t join utilizing the DA

How to Enable Dedicated Administrator Connection (DAC) for SQL Server instance?
To enable DAC, Connect to the instance and open a new query window, run the following command

sp_configure ‘remote admin connections’, 1;
GO
RECONFIGURE;
GO

How to verify if Dedicated Administrator Connection (DAC) is enabled on a SQL Server Instance?
We can verify or confirm that Dedicated Administrator Connection (DAC) is working by connecting to SQL Server instance using the following in the “Instance” field of SSMS login window

ADMIN:

How many Dedicated Administrator Connections (DAC) can be opened at a time for a particular SQL Server instance?
Only one connection can be made using Dedicated Administrator Connections (DAC).

When is the Dedicated Administrator Connections (DAC) to be used?
Dedicated Administrator Connections (DAC) can be used in cases where SQL Server is running but not responding to any regular connections due to resource usage. DAC connection has its own memory and scheduler and dedicated thread, which allows connection through DAC to run queries even when the SQL Server is not responding. It is not advisable to run a large or complex queries with DAC, rather it is to be used to identify the resource consuming queries causing SQL Server to hang or to not respond and kill any such offending sessions.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

SQL Server Instance Level Objects Backups

SQLServerF1

One of the important and common responsibility of a SQL Server DBA is to make sure a perfect backup/recovery plan is in place and that the plan can be implemented as per the SLA’s. Periodic testing is important to ensure that the plan works in case of unexpected disaster. In most of the environments, the backups of SQL Server databases are performed which include system and user databases, however there can be many scenarios where in one of the database may be corrupted and has to be recovered, in which case the restore of the database backups works great, but in some cases it is also a possibility that certain server level objects have to be recovered and restoring system databases is not an option and a time taking option. In such cases have backup of server level objects separately will serve greatly.

Thus it becomes important for a DBA to also consider performing backups of instance level objects periodically, so that the level of restores or control on the objects which required to be restored will be available. Also this will act as a secondary option in case the primary option of system database backups does not work in some situations. Below are the important SQL Server instance level objects which are to be backed up regularly and can be used later when required.

– SQL Server Instance Configuration properties: these setting can be backed up by saving the output of sp_configure values or the values of select * from sys.sysconfigurations.
– Database properties: The database properties gets backed up by the database backups, however saving these settings in a text document will be useful to keep track of settings and can be verified or compared if the values are changed by someone and we had to identify what were the values DBA team planned for.
– DDL triggers: There are server level triggers which should be backed up and will be useful if someone deletes the triggers.
– Backup Devices: During a backup operation on a SQL Server database, the backed up data (the backup) is written to a physical backup device. This physical backup device is initialized when the first backup in a media set is written to it. The backups on a set of one or more backup devices compose a single media set.

– Endpoints: SQL Server endpoint is the point of entry into SQL Server. It is implemented as a database object that defines the ways and means in which SQL Server may communicate over the network. SQL Server routes all interactions with the network via endpoints and each endpoint supports a specific type of communication.
– SQL Server Agent Properties: These settings are important and the backup of these settings are useful in cases where these values are changed by someone and we want to know what are the correct ones.
– SQL Server Agent Jobs: Very important to have backup of the jobs, so that we can recreate them easily without having to restore the MSDB database. It is important to keep in mind that some jobs will run DTS/SSIS packages which have be backed up separately where the packages have to be saved separately.
Linked Servers: Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel.
SQL Server Logins: Logins allow access to users to connect to SQL Server, so important to keep a backup, so that we can recreate the required logins if they are accidentally deleted.

Other SQL Server instance level objects which can be backed up include SQL Server Agent operators, SQL Server Agent alerts, SQL Server Server Roles, SQL Server Audits, etc

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Reviewing Security Configuration of SQL Server

SQLServerF1

Security is one of the most important aspects for any technology. As SQL Server is an RDBMS product which stores users data which is important and critical, so it is common to see many attempts been made to compromise and get access to the important user or business related data. This brings responsibility of the DBAs, developers, management to plan and implement best security practices to avoid any such attacks from succeeding. It is important to periodically review the security settings to make sure that there are no gaps or possibilities of security breach. Below are list of items or objects which a DBA team may want to periodically review and verify to make sure that the security configuration is as per best practices.

Database Owners: By default, any users who creates the databases becomes the owner of the database. Mostly DBAs who create the databases become the owner of that database, so it is important to immediately change the database owner to SA or other standard account. Periodically review the database owners to make sure there are no user specific domain accounts being the database owners.
Windows or SQL Server Logins: Periodically review all the windows logins, SQL Server logins and windows groups which has access to the SQL Server instance. Remove any logins which does not require access anymore. It happens that some user leaves the company but the logins still remain. Sometimes access is given on temporary basis for a project work and would not removed after the completion of the project. Periodic review of the logins should resolve this issue of unauthorized logins being present at server level or at SQL Server level.

Server Level Permissions: There are different kinds of server level and database level roles provided by SQL Server. Make sure that only the required permissions are granted to the users. Providing more permissions than what is required will cause serious problems. It is important to periodically review and revoke any additional accesses granted to any users.
SQL Agent Jobs Owned by Windows Domain Accounts: The DBA who creates a SQL Server Agent job by default becomes owner of that job, so make sure to change it to SA
.SQL Server Client Protocols: By default SQL Server allows communication through ports(TCP/IP, Shared Memory, Named Pipes, VIA). Mostly it is not required for all these protocols to be enabled, some of them can be disabled when application does not use them, so that any one trying to gain access through that protocol would be blocked.

SQL Server TCP/IP port: Default instance of SQL Server uses port 1433 by default, so it is advisable to change to this some other port other than 1433 as per best practices. But making this change may cause applications to fail connecting to SQL instance, so care has to be taken to make changes to application connection string to use the port number used by SQL Server or to enable SQL Browser service. SQL Server Named instances can be configured to use either permamnent statis port or a dynamic port which may change after SQL or server restart. It is advisable to configure static port for SQL Server Named instances.

The above checks or recommendations are high level and there are mode advanced check that are to be performed for more critical servers. There are compliances like SOX HIPAA which put rules to be followed for critical servers. Also, care should be taken to implement the recommendations and proper testing has to be done prior to any implementation to make sure applications does not break due to the changes.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 
1 2