Author Archive

SQL Server – Mirror Backup

Posted: November 7, 2016 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , , , ,
0

As a DBA it’s a TOP PRIORITY to make sure that all database backups should be useful in case of any Disaster and should know how to recover database from a Disaster.
In Real time, with every SQL Server environment, we regularly take database backup on various available backup devices. With SQL Server 2005 onwards, there is an option of Mirror Backup with enterprise edition. Using Mirroring a backup media set increases backup reliability by reducing the impact of backup-device failure. These type of failure sometime becomes very serious because backups are the last heartbeats against data loss. In current era, database sizes are increasing drastically, and as a resultant the failure of a backup device or media will make a backup non restorable. Mirroring backup media increases the reliability of backups by providing redundancy.

As per below given example, here I am taking backup at two locations,


Point to be Note here is the number of media families and mirrors is defined when the media set is created by a BACKUP DATABASE statement that specifies WITH FORMAT keyword.

Keep reading
J
!.

SQL Server: The Best Practices on Indexing

Posted: November 4, 2016 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , , , , , ,
0

Here, I am writing some indexing related the best practices during index creation with SQL Server DB’s tables.

  • Periodically, run the Index Wizard or Database Engine Tuning Advisor against current Profiler traces to identify potentially missing indexes.
  • Check and remove unused indexes.
  • Its not a thumb rule to get optimized performance by creating Indexes, First check and create index instead of creating redundant indexes.
  • As a rule of thumb, every table should have at least a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases — such as an identity column, or some other column where the value is increasing — and is unique. In many cases, the primary key is the ideal column for a clustered index.
  • As we can have only one clustered index per table, take extra time to carefully consider how it will be used. Consider the type of queries that will be used against the table, and make an efficient judgement to which query is the most critical, and if this query will benefit from having a clustered index.
  • If a column in a table is not at least 95% unique, then most likely the query optimizer will not use a non-clustered index based on that column. Because of this, generally don’t want to add non-clustered indexes to columns that aren’t at least 95% unique.
  • Keep the width of indexes as narrow as possible. This reduces the size of the index and reduces the number of disk I/O reads required to read the index, boosting performance.
  • If possible, avoid adding a clustered index to a GUID column. GUIDs take up 16-bytes of storage, more than an Identify column, which makes the index larger, which increases I/O reads, which can hurt performance.
  • Try to avoid index on Timestamp column, its extra burden for I/O.
  • Indexes should be considered on all columns that are frequently accessed by the JOIN, WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.
  • Don’t automatically add indexes on a table because it seems like the right thing to do. Only add indexes if you know that they will be used by the queries run against the table.
  • When creating indexes, try to make them unique indexes if at all possible. SQL Server can often search through a unique index faster than a non-unique index because in a unique index, each Row is unique, and once the needed record is found, SQL Server doesn’t have to look any further.
  • If we are regularly using joins between two or more tables in your queries, performance will be optimized if each of the joined columns has appropriate indexes.
  • Don’t automatically accept the default value of 100 for the fill factor for your indexes. It may or may not best meet your needs. A high fill factor is good for seldom changed data, but highly Modified data needs a lower fill factor to reduce page splitting.
  • Don’t over index OLTP tables, as every index will increases the time it takes to perform INSERTS, UPDATES, and DELETES. There is a fine line between having the ideal number of Indexes (for SELECTs) and the ideal number to minimize the overhead that occurs with indexes during data modifications.
  • If it’s known, an application is performing the same query over and over on the same table, consider creating a non-clustered covering index on the table. A covering index, which is a form of a composite index, includes all of the columns referenced in SELECT, JOIN, and WHERE clauses of a query.

    Happy Reading: Please Comment !

I’M SPEAKING AT SQL SATURDAY #562, MALAYSIA 2016

Posted: August 31, 2016 by Virendra Yaduvanshi in Database Administrator
Tags: , , ,
1

sqlsat_speakingat2

Enhancing securities with Row Level Security (RLS)

Posted: June 3, 2016 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , , ,
2

SQL Server 2016 has introduced Row Level Security (RLS) – it’s an enhanced security feature that enables control over access to rows in a table. For general security purpose there are also many features like “Always Encrypted”, “Dynamic Data Masking”, “enhancement of Transparent Data Encryption”, but RLS allows us to easily control which users can access which data with complete transparency to the application. This enables us to easily restrict the data based on the user identity or security context. Row-Level Security in SQL Server 2016 helps us to maintain a consistent data access policy and reduce the risk of accidental data leakage.
Actually, Row Level Security (RLS) is a concept that provides security at the row level within the database layer, instead of at the application layer.  RLS may be implemented by using a function and a new security policy feature without even changing application code.

Before implementing RLS, First, we should know the few new terms which one need to learn and understand this feature.

Security Predicate: This is not a new object but an inline table valued function -inline TVF -which contains the logic of filtering the rows.

Security Policy: This is a new object which can be CREATE, ALTER and DROP. It may be consider as a container of predicates which can be applied to tables. One policy can contain security predicate to many tables. A policy can be in an ON or OFF state.

RLS supports two types of security predicates.

  • Filter predicates silently filter the rows available to read operations (SELECT, UPDATE, and DELETE).
  • Block predicates explicitly block write operations (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE) that violate the predicate.

Let’s take an example :

–Create a Database named “VirendraRLS_Test”

CREATE DATABASE VirendraRLS_Test; 
GO

USE VirendraRLS_Test;
GO

— Create users

CREATE USER Viren WITHOUT LOGIN;
CREATE USER Yaduvanshi WITHOUT LOGIN;
GO
— Create a table with sample data and grant SELECT to the new users
CREATE TABLE dbo.Employee
( [EmployeeName] VARCHAR(25),[BasicSalary] MONEY)
GO

INSERT INTO dbo.Employee
VALUES (‘Viren’,1000),(‘Yaduvanshi’,1200),(‘viren’,1450)
GO

GRANT SELECT ON dbo.Employee TO Viren
GRANT SELECT ON dbo.Employee TO Yaduvanshi
GO
— Create a filter predicate function as below
CREATE FUNCTION dbo.RLSPredicate (@EmployeeName as sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT 1 AS RLSPredicateResult WHERE @EmployeeName = USER_NAME();
GO
— Add filter predicate to the table
CREATE SECURITY POLICY RLSUserFilter
ADD FILTER PREDICATE dbo.RLSPredicate(EmployeeName)
ON dbo.Employee WITH (STATE=ON);
GO

EXECUTE (‘SELECT * FROM Employee’) AS USER=‘Viren’
EXECUTE (‘SELECT * FROM Employee’) AS USER=‘Yaduvanshi’


Microsoft takes step to move beyond Windows operating system : MS-SQL Server on Linux Operating System.

Posted: March 8, 2016 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , , ,
0

As per Microsoft, MS is planning to put one of its main products MS-SQL Server on Linux for the first time.

Here is the complete details.

https://blogs.microsoft.com/blog/2016/03/07/announcing-sql-server-on-linux/

Waiting enthusiastically for same !

SQL Server 2014 : Buffer Pool Extension (BPE)

Posted: February 10, 2016 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , , , , , , ,
3

Buffer pool extension introduced in SQL server 2014. The buffer pool extension provides the seamless integration of a nonvolatile RAM extension to the Database Engine buffer pool to significantly improve I/O throughput. As we know the primary purpose of a SQL Server Database is to store and retrieve data, in this operation, commonly Data is read from disk into memory, once the data is changed, it is marked as dirty and the dirty pages are written to disk and flagged as clean data. clean pages may be flushed from memory when the data cache known as Buffer Pool comes under pressure and in this situation data got deleted from memory and SQL Server has to read it from disk the next time a user runs a query that requires the data.
Now there are no problems as data size is less than memory, but as data size is more than memory – It’s started creating issues, To resolve this, In SQL Server 2014 Buffer Pool Extensions introduced to solve problem if we have not enough memory. In the case of Buffer Pool Extensions, SQL Server uses the disk space to store clean buffers having unmodified data pages that out of RAM.
The buffer pool extension feature extends the buffer pool cache with nonvolatile storage (usually SSD). Because of this extension, the buffer pool can accommodate a larger database working set, which forces the paging of I/O’s between RAM and the SSDs. This effectively offloads small random I/O’s from mechanical disks to SSDs. Because of the lower latency and better random I/O performance of SSDs, the buffer pool extension significantly improves I/O throughput.

SSD storage is used as an extension to the memory subsystem rather than the disk storage subsystem. That is, the buffer pool extension file allows the buffer pool manager to use both DRAM and NAND-Flash memory to maintain a much larger buffer pool of lukewarm pages in nonvolatile random access memory backed by SSDs. This creates a multilevel caching hierarchy with level 1 (L1) as the DRAM and level 2 (L2) as the buffer pool extension file on the SSD. Only clean pages are written to the L2 cache, which helps maintain data safety. The buffer manager handles the movement of clean pages between the L1 and L2 caches.

The following illustration provides a high-level architectural overview of the buffer pool relative to other SQL Server components.

When enabled, the buffer pool extension specifies the size and file path of the buffer pool caching file on the SSD. This file is a contiguous extent of storage on the SSD and is statically configured during startup of the instance of SQL Server. Alterations to the file configuration parameters can only be done when the buffer pool extension feature is disabled. When the buffer pool extension is disabled, all related configuration settings are removed from the registry. The buffer pool extension file is deleted upon shutdown of the instance of SQL Server.

Best Practices

  • The buffer pool extension size can be up to 32 times the value of max_server_memory.
  • A ratio between the size of the physical memory (max_server_memory) and the size of the buffer pool extension of 1:16 or less. A lower ratio in the range of 1:4 to 1:8 may be optimal.

Limitations/Recommendations

  • BPE feature is available for 64-bit SQL Server only.
  • Its available with SQL Server 2014 Standard, Business Intelligence and Enterprise only.
  • If BPE is enabled and you suppose have to modify the size of the file used by BPE on the non-volatile disk, then first need tp disable BPE and set the new size and enable BPE again. If the size is less than previously set, SQL Server must be restarted for the changes to take effect on the non-volatile disk.

Implementing SSD Buffer Pool Extension

— Enabling BPE as 50GB

ALTER SERVER CONFIGURATION 
  SET BUFFER POOL EXTENSION ON 
  (FILENAME = ‘F:\SSD_Data\VirendraTest.BPE’,SIZE = 50 GB)

— Disable BPE

ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF

— To See BPE status

SELECT
  (CASE WHEN ([is_modified] = 1 AND ([is_in_bpool_extension] IS NULL OR [is_in_bpool_extension] = 0)) THEN N’Dirty’
WHEN ([is_modified] = 0 AND ([is_in_bpool_extension] IS NULL OR [is_in_bpool_extension] = 0)) THEN N’Clean’
WHEN ([is_modified] = 0 AND [is_in_bpool_extension] = 1) THEN N’BPE’ END) AS N’Page State’,
(CASE WHEN ([database_id] = 32767) THEN N’Resource Database’ ELSE DB_NAME ([database_id]) END) AS N’Database Name’, 
COUNT(1) AS N’Page Count’
FROM sys.dm_os_buffer_descriptors 
GROUP BY [database_id], [is_modified], [is_in_bpool_extension]
ORDER BY [database_id], [is_modified], [is_in_bpool_extension]

Happy Reading : Please Comment !

Improving SQL Query Performance

Posted: December 24, 2015 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , , , , , , , ,
4

As SQL query performance improvement is a very debating topic between developers and the other user community. Users always wants a fast response on their data retrieval action and developers put forth their best efforts to provide the data in the minimum time span, however, there is no straightforward way to define what is the best performance. Sometime it’s debatable what is good and what bad performance of a query is but overall we have to follow best practices during development, a good developer may provide the best query response to users and avoid such discussions. We can choose multiple ways to improve SQL query performance, which falls under various categories like re-writing the SQL query, creation and use of Indexes, proper management of statistics….. and other best practices. Here are some top tips for this regards.

Avoid Correlated Subqueries

Subqueries are a very powerful and useful feature of the SQL standard. Subqueries can be categorized as either correlated or uncorrelated queries. A correlated subquery is one that is dependent on the outer query to be processed. These types of subqueries can be very inefficient and should be avoided. We can use temp table and join with outer query or other method to get the result.

Eliminate Cursors from the Query

Try to remove cursors from the query and use set-based query; set-based query is more efficient than cursor-based. A good SQL programmer must develop the mental discipline to explore set-based possibilities thoroughly before falling back on the intuitive procedural solution If there is a need to use cursor than avoid dynamic cursors as it tends to limit the choice of plans available to the query optimizer. For example, dynamic cursor limits the optimizer to using nested loop joins.

Avoid Multiple Joins in a Single Query

Try to avoid writing a SQL query using multiple joins that includes outer joins, cross apply, outer apply and other complex sub queries. It reduces the choices for Optimizer to decide the join order and join type. Sometime, Optimizer is forced to use nested loop joins, irrespective of the performance consequences for queries with excessively complex cross apply or sub queries

Avoid Use of Non-correlated Scalar Sub Query

We can re-write our query to remove non-correlated scalar sub query as a separate query instead of part of the main query and store the output in a variable, which can be referred to in the main query or later part of the batch. This will give better options to Optimizer, which may help to return accurate cardinality estimates along with a better plan.

Avoid Multi-statement Table Valued Functions (TVFs)

Multi-statement TVFs are more costly than inline TFVs. SQL Server expands inline TFVs into the main query like it expands views but evaluates multi-statement TVFs in a separate context from the main query and materializes the results of multi-statement into temporary work tables. The separate context and work table make multi-statement TVFs costly.

Creation and Use of Indexes

As many DBA/Developer creates index to magically reduce the data retrieval time but have a reverse effect on DML operations, which may degrade query performance. With this fact, Indexing is a challenging task, but could help to improve SQL query performance and give us best query response time.

Index on Highly Selective

Selectivity define the percentage of qualifying rows in the table (qualifying number of rows/total number of rows). If the ratio of the qualifying number of rows to the total number of rows is low, the index is highly selective and is most useful. A non-clustered index is most useful if the ratio is around 5% or less, which means if the index can eliminate 95% of the rows from consideration. If index is returning more than 5% of the rows in a table, it probably will not be used; either a different index will be chosen or created or the table will be scanned. Here is a query to find the index status.
SELECT statement AS [database.scheme.table],
column_id , column_name, column_usage,
migs.user_seeks, migs.user_scans,
migs.last_user_seek, migs.avg_total_user_cost,
migs.avg_user_impact
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns(mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats AS migs ON mig.index_group_handle=migs.group_handle
ORDER BY migs.user_seeks, migs.user_scans
GO

Column order in an Index

Order or position of a column in an index also plays a vital role to improve SQL query performance. An index can help to improve the SQL query performance if the criteria of the query matches the columns that are left most in the index key. As a best practice, most selective columns should be placed leftmost in the key of a non-clustered index. The above query will let us know about Column positioning also.

Drop Unused Indexes

Dropping unused indexes can help to speed up data modifications without affecting data retrieval. Also, we need to define a strategy for batch processes that run infrequently and use certain indexes. In such cases, creating indexes in advance of batch processes and then dropping them when the batch processes are done helps to reduce the overhead on the database. Here is a query to find unused indexes.

SELECT ‘[‘+ DB_NAME() + ‘].[‘ + su.[name] + ‘].[‘ + o.[name] + ‘]’ AS [statement] ,i.[name] AS [index_name] ,
ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] AS [user_reads] ,ddius.[user_updates] AS [user_writes] ,
SUM(SP.rows) AS [total_rows]
FROM sys.dm_db_index_usage_stats ddius
INNER JOIN sys.indexes i ON ddius.[object_id] = i.[object_id] AND i.[index_id] = ddius.[index_id]
INNER JOIN sys.partitions SP ON ddius.[object_id] = SP.[object_id] AND SP.[index_id] = ddius.[index_id]
INNER JOIN sys.objects o ON ddius.[object_id] = o.[object_id]
INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID]
WHERE ddius.[database_id] =DB_ID() — current database only
AND OBJECTPROPERTY(ddius.[object_id], ‘IsUserTable’)= 1  AND ddius.[index_id] > 0
GROUP BY su.[name] ,o.[name] ,i.[name] ,ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] ,ddius.[user_updates] HAVING ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] = 0
ORDER BY ddius.[user_updates] DESC,su.[name] ,o.[name] ,i.[name ]

Statistic Creation and Updates

We have to take care of statistic creation and regular updates for computed columns and multi-columns referred in the query, the query optimizer uses information about the distribution of values in one or more columns of a table statistics to estimate the cardinality, or number of rows, in the query result. These cardinality estimates enable the query optimizer to create a high-quality query plan.

Happy Reading ! .. Like to hear your valuable comments…

Only Truncate Table permission to specific user

Posted: November 4, 2015 by Virendra Yaduvanshi in Database Administrator
Tags: , , , ,
2

Let in your environment there are lots of SQL user and as a DBA you have to give permission to a specific user to truncate table only. The specified User have no select/delete/insert/update permission on table.

In this scenario we have to grant ALTER permission to that user.

USE [VirendraTest] — VirendraTest is here database Name
GO

GRANT ALTER ON [dbo].[TestTable] TO [Virendra] — Virendra is a User
GO

 

How To : Reporting Server (SSRS) – Activity Details

Posted: August 20, 2015 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , , , , ,
1

Hi Guys, Here I am starting How To series, hope it will be helpful for SQL’s lovers.

It’s a daily routine we see system reports where all things like Server health, DB Status, Jobs Status, Disk Spaces … etc. are as per defined standard or not. Hope you guys also worked/managed SSRS – report server also. Hope you have also got queries / complaints from your internal or external clients stating reports are slow / reports are not being generated / reports server is not working / reports are being generated from long time …… etc.
Here is a query, it will show where are problems – is it related to data Retrieval or related to data processing or where it is related to rendering. Below query will show all details

Use ReportServer — Use configured DB Name
GO

SELECT EL.ReportID ‘Report ID’,
CT.name ‘Report Name’,
CT.Path ‘Report Path’,
CASE
WHEN EL.RequestType = 0 THEN ‘Interactive’
WHEN EL.RequestType = 1 THEN ‘Subscription’
WHEN EL.RequestType = 2 THEN ‘Refresh Cache’ END AS ‘Request Type’,
EL.Format ‘Report Format’,
EL.TimeStart,
EL.TimeEnd,
DATEDIFF(ss,EL.TimeStart,EL.TimeEnd) AS ‘TotalDuration(Sec)’,
(EL.TimeDataRetrieval/1000.00) AS ‘Data Retrieval Time (Sec)’,
(EL.Timeprocessing/1000.00) AS ‘Processing Time(Sec)’,
(EL.TimeRendering/1000.00) AS ‘Rendering Time(Sec)’,
CASE
WHEN EL.Source=1 THEN ‘LIVE’
WHEN EL.Source=2 THEN ‘Cache’
WHEN EL.Source=3 THEN ‘Snapshot’
WHEN EL.Source=4 THEN ‘History’
WHEN EL.Source=5 THEN ‘Ad hoc(Report Builder)’
WHEN EL.Source=6 THEN ‘Session’
WHEN EL.Source=7 THEN ‘Report Definition Customization Extension(RDCE)’
END AS ‘Source’,
EL.Status,
EL.ByteCount/1024.00 AS ‘Size(Kb)’,
EL.[RowCount] AS ‘Number of Records’
FROM ExecutionLog EL
INNER JOIN [Catalog] CT ON CT.itemid=EL.reportid
Order by EL.TimeStart Desc

Use it and please share the comments/views/your finding on same – Happy Reading !

Older Entries
Newer Entries