Blog Archives

SQL Tip – Comma delimited list from a table column

If you want to extract query result of a column to a variable as Comma separated values list use the following example and modify accordingly. declare @Dblist varchar (max) set @Dblist = ” select @Dblist = case when @Dblist =

Tagged with:
Posted in sql, SQL Server, tsql

Microsoft SQL Server Performance Dashboard Reports – 1 – Intro & Default Install

Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem.
CPU bottlenecks (and what queries are consuming the most CPU)
IO bottlenecks (and what queries are performing the most IO)
Index recommendations generated by the query optimizer (missing indexes)
Blocking
Latch contention

Tagged with: , , , , ,
Posted in reporting, sql, SQL Server, sql server performance

SQL Tip: Changing Database names along with file names (logical and physical)

Sometimes you need to rename a databases. Although it seems pretty easy to rename it on the surface. However for long term maintainability it is crucial that the underlying logical files and physical files also reflect the changes. Without the

Tagged with:
Posted in security, sql, SQL Server, tsql

SQL Tip: How to find SQL Server installation date and time

Getting right down to business… heres the script SELECT create_date FROM sys.server_principals WHERE sid = 0x010100000000000512000000 And for Eval versions — Evaluation Version Expire Date SELECTcreate_date ASInstallationDate, DATEADD(DD, 180, create_date) AS’Expiry Date’ FROMsys.server_principals WHEREsid = 0x010100000000000512000000 GO Reference: Pinal Dave (http://blog.sqlauthority.com)

Tagged with:
Posted in sql, SQL Server, tsql, Uncategorized

SQL Server 2016 new features & common performance issue of Tempdb Contention

Tempdb the “shared resource” or the “scratch pad” or even the “waste bin” as described by many names in SQL Server has of course a lot many important things to do but by very nature of any shared resource there

Tagged with: , ,
Posted in log, sql, SQL Server, Uncategorized

SQL Server Restoring backups from UNC Network paths and Recovery Time Objectives

Might sound very basic but I still come across colleagues who are confused on this topic. SQL Server management studio when restoring backups does no let you browse to network drives. That is why if quick workarounds are not known people

Tagged with: , ,
Posted in backup, sql, SQL Server

Usage data for SSRS reports to decide on reports to consider for re-engineering & retiring

You have a SSRS instance on either live or production and you want to find out which reports and most / least used. Luckily SQL Server maintains by default SSRS usage info in audit logs it maintains. Although you can

Tagged with: , , ,
Posted in sql, SQL Server, ssrs, tsql

Encrypt SQL Server connections

SQL Server connections can and should be encrypted where possible but must when passing through public circuits. The overview of the process of setting up SQL Server connection encryption is as follows. Get a certificate issued by the CA in

Tagged with: , , , , ,
Posted in security, sql, SQL Server

SQL Server Index and Statistics maintenance: Importance and a good solution with explanation (Ola hallengren’s IndexOptimize)

INTRODUCTION Index and Statistics need to be maintained regularly for optimum performance of a SQL Server database engine. The SQL Server core engine within the Query Optimizer part of it, uses Indexes and statistics to create a query plan to

Tagged with: , ,
Posted in sql, SQL Server, tsql

Running Diagnostic TSQL scripts remotely within and without SCOM- 1

Requirement: To be able to run a generic diagnostic information gathering script remotely via System Center Operations Manager on a SQL Server instance without having to start a separate session of SSMS and / or RDP and executing script. There

Tagged with: , , , ,
Posted in Development, Monitoring, SCOM, sql, SQL Server, tsql