Archive for November, 2013

What does it take to Blog? November 26th, 2013

Vinod Kumar

Offlate I have a number of folks who are on my FB list asking me how I manage to write something every single day with Good Morning messages. The idea of writing is a state of mind, it is the will power to do something consistently, every single day without any strain or stress. To become an efficient writer, it is about knowing what to do, when to do it and how to do it. Let me give you my take using simple steps:

1. Build your Routine

The biggest thing about writing is to make time for writing. I am sure everyone has a full-time job, so write daily, write routinely, write often and write consistently.

2. Create your mindmap

Before you write, think what you want to write and how you want to lay out your thoughts. Write your outline and what message you want to convey as part of each post.

3. Draft it to Rewrite

Once your have your rough outline, it is important to draft out some of the content that you want to land. Don’t try to edit as you write the first time. The first draft must capture your flow of thoughts. So just dump your thoughts.

Once you have that, read the content and reword accordingly. Few people have a tendency to rest their first draft for 2-3 days, they sleep over the article. With each hour that passes, the article gets better.

4. Look for Positive intent

As you read your draft, make sure there are no negative tone in your article. I personally feel this is an important part. As you write about a topic, the inner voice might pull you down – don’t fall into this trap. There are 1000’s out there who still want your perspective on the same topic which is out there in 1000 other article. So don’t let the negative thoughts/people pull you down.

5. Be Confident

Every writer doesn’t start confident, there is always a sense of insecurity as they write. It is all part of the game. As you keep writing, you will feel fine. You will keep writing and you will gain confidence. Just chip it, day after day every single day.

6. Read everyday

As writers, read as much as possible. I personally read quite a bit. I get inspiration from a number of books. I read these books to know how one structures their thoughts and there is lots to learn from people around you. It helps in our writing styles too.

As I wrap up this article. Let me end it saying – just get in front of your computer and start writing. The best way you can become a writer is to start refusing to listen to own excuses. Trust me, writing is never easy – I would say stop procrastinating. Your best inspiration is the urge to share – sit down and write: Don’t wait.

Continue reading...


 

SQL Server: Finding IO load distribution November 15th, 2013

Vinod Kumar

This blog post is in continuation to the various scripts I am putting online. This script is based on a customer requirement where they were buying few SSDs and were wanting to know what files / databases need to be moved into this fast drives. We need to start somewhere and the first query gives you a high-level of all the IO reads/writes across all the databases.

SELECT *
FROM   sys.DM_IO_VIRTUAL_FILE_STATS (NULL, NULL)

This is a great set of information to assimilate but if you want to look at all the drives inside a given server and want to move specific files from slowly/stressed drives to this new drive, this below query will surely help you.

SELECT LEFT(MF.physical_name, 1)     AS DRIVE_LETTER,
sample_ms,
SUM(VFS.num_of_writes)        AS TOTAL_NUM_OF_WRITES,
SUM(VFS.num_of_bytes_written) AS TOTAL_NUM_OF_BYTES_WRITTEN,
SUM(VFS.io_stall_write_ms)    AS TOTAL_IO_STALL_WRITE_MS,
SUM(VFS.num_of_reads)         AS TOTAL_NUM_OF_READS,
SUM(VFS.num_of_bytes_read)    AS TOTAL_NUM_OF_BYTES_READ,
SUM(VFS.io_stall_read_ms)     AS TOTAL_IO_STALL_READ_MS,
SUM(VFS.io_stall)             AS TOTAL_IO_STALL,
SUM(VFS.size_on_disk_bytes)   AS TOTAL_SIZE_ON_DISK_BYTES
FROM   sys.master_files MF
JOIN sys.DM_IO_VIRTUAL_FILE_STATS(NULL, NULL) VFS
ON MF.database_id = VFS.database_id
           
AND MF.file_id = VFS.file_id
GROUP  BY LEFT(MF.physical_name, 1),
         
sample_ms 

As part of drives look for IO stalls and based on higher value look at moving load from the stressed drive to another.

Continue reading...


 

SSRS Tuning Query-Part III November 14th, 2013

Vinod Kumar

This is in continuation to SSRS tuning Query–Part II series that I have been writing. This particular query is to parse the AdditionalInfo XML data for the TOP 10 longest duration reports from our Reporting Server environments.

SELECT TOP 10
COALESCE(C.path, ‘Unknown’)
AS ReportPath,
Datediff(s, timestart, timeend)
AS ReportExecutionDuration_Seconds,
els.[rowcount],
CASE els.source
 
WHEN 1 THEN ‘Live’
 
WHEN 2 THEN ‘Cache’
 
WHEN 3 THEN ‘Snapshot’
 
WHEN 4 THEN ‘History’
 
WHEN 5 THEN ‘AdHoc’
 
WHEN 6 THEN ‘Session’
 
WHEN 7 THEN ‘Rdce’
 
ELSE ‘Unknown’
END
AS Source,
CASE( reportaction )
 
WHEN 1 THEN ‘Render’
 
WHEN 2 THEN ‘BookmarkNavigation’
 
WHEN 3 THEN ‘DocumentMapNavigation’
 
WHEN 4 THEN ‘DrillThrough’
 
WHEN 5 THEN ‘FindString’
 
WHEN 6 THEN ‘GetDocumentMap’
 
WHEN 7 THEN ‘Toggle’
 
WHEN 8 THEN ‘Sort’
 
ELSE ‘Unknown’
END
AS ReportAction,
CASE
els.additionalinfo.value(‘(/AdditionalInfo/ProcessingEngine)[1]’, ‘integer’
)
               
WHEN 1 THEN ‘SQL 2005’
               
WHEN 2 THEN ‘On-Demand’
               
ELSE ‘Unknown’
             
END
             
AS ProcessingEngine,
             
els.timerendering,
             
els.timeprocessing,
els.additionalinfo.value(‘(/AdditionalInfo/ScalabilityTime/Pagination)[1]’,
‘integer’)
AS
PaginationScalabilityTime,
els.additionalinfo.value(‘(/AdditionalInfo/ScalabilityTime/Rendering)[1]’,
‘integer’)
AS
RenderingScalabilityTime,
els.additionalinfo.value(‘(/AdditionalInfo/ScalabilityTime/Processing)[1]’,
‘integer’)
AS
ProcessingScalabilityTime,
els.additionalinfo.value(‘(/AdditionalInfo/EstimatedMemoryUsageKB/Pagination)[1]’, ‘integer’) AS PaginationMemoryKB,
els.additionalinfo.value(‘(/AdditionalInfo/EstimatedMemoryUsageKB/Rendering)[1]’, ‘integer’)  AS RenderingKB,
els.additionalinfo.value(‘(/AdditionalInfo/EstimatedMemoryUsageKB/Processing)[1]’, ‘integer’) AS ProcessingKB
FROM   executionlogstorage els
INNER JOIN dbo.catalog c
ON c.itemid = els.reportid
ORDER  BY reportexecutionduration_seconds DESC

This search for newer and more interesting queries will continue and I will keep sharing some of these interesting queries with you from time to time as I work with multiple customer environments. Hope you are finding it interesting.

Continue reading...


 

SQL Security– Demystifying Principals, Securables and Permissions November 5th, 2013

Vinod Kumar

I have written other blog posts around SQL Server: Security Practices and Checklist before. But many of my customers come back to basics and ask questions around what Principals, securables and permissions are. This blog post is inspired to those folks who want to understand from the basic. In future posts, I will dwell into more details into each of these security topics.

What are Principals?

A principal is any authenticated identity that can be given permission to access an object in the database system. SQL Server documentation distinguishes between indivisible principals, which are single identities (such as logins), and collection principals, which are collections of identities (such as fixed server roles).

Principals exist at three levels: Windows, SQL Server, and database. The types of principals possible at each of these levels are shown in the following table.

Level

Principals

Windows

Windows local user account

Windows domain user account

Windows group

SQL Server

SQL Server login

SQL Server role

Database

Database user

Database role

Database group

Application role

What are Securables?

Securables are the resources to which the SQL Server authorization system controls access. Securables are arranged in nested hierarchies called scopes, which can also be secured. The three securable scopes are server, database and schema. Securables at the Windows level include files and registry keys.

The Server Scope

Securables contained in the server scope include:

  1. Logins
  2. HTTP endpoints
  3. Certificates
  4. Event notifications
  5. Databases
The Database Scope

Securables contained in the database scope include:

  1. Users
  2. Roles
  3. Application roles
  4. Assemblies
  5. Message types
  6. Service contracts
  7. Services
  8. Full-text catalogs
  9. DDL events
  10. Schemas
The Schema Scope

Securables contained in the schema scope include:

  1. Tables
  2. Views
  3. Functions
  4. Procedures
  5. Queues
  6. Types
  7. Rules
  8. Defaults
  9. Synonyms
  10. Aggregates

What are Permissions?

Permissions are the rules that govern the level of access that principals have to securables. Permissions in a SQL Server system can be granted, revoked, or denied. Each of the SQL Server securables has associated permissions that can be granted to each principal. SQL Server 2005 introduces the ability to grant permissions at the server scope. In previous releases of SQL Server, server-level permissions were managed using fixed server roles.

The specific permissions associated with individual securables vary depending on the kinds of actions that a securable supports. The following table lists a few example permissions at various scopes. For a full list of permissions, see SQL Server Books Online. Below is a sample set:

Securable

Permission

Description

Server

CONNECT_SQL

Connect to the server.

Server

CREATE LOGIN

Create a login.

Server

ALTER ANY LOGIN

Alter any login in the server scope.

Server

CONTROL SERVER

Full system administrative control.

Login

ALTER

Alter the login.

Login

IMPERSONATE

Impersonate the login.

Database

CREATE TABLE

Create table in the database.

Database

ALTER ANY USER

Alter any user in the database.

Database

CONTROL

Full control of the database.

User

ALTER

Alter the specified user.

Schema

SELECT

Select rows from any object in the schema.

Schema

ALTER

Alter any object in the schema.

Schema

TAKE OWNERSHIP

Take ownership of the schema.

Table

SELECT

Select rows from the table.

Table

ALTER

Alter the table.

Table

CONTROL

Full control of the table.

From SQL Server 2005 we support inherited permissions for securables contained in a given scope. For example, a principal granted CONTROL permission on a database object will automatically inherit CONTROL permission on all securables contained in that database and all securables contained in the schemas within that database

As I sign off, want to understand how many of you take security seriously in your SQL Server deployments? How many out there still use the SA accounts in your environments and application connections? Please understand, Security is not an after thought but something part of your fundamental application deployments.

Continue reading...


 

SQL Server AlwaysOn-how far behind is Secondary November 4th, 2013

Vinod Kumar

If you ever wondered why I wrote a simple post like SQL Server AlwaysOn-Find my Primary, this post is really for you. There are a lot of times customers ask me how much time is my secondary behind my primary? Such a simple question can be answered easily using some good DMVs. Here is the script I use extensively when working with AlwaysOn customers to identify what is doing behind the scene’s in their setup.

SELECT AGS.name                       AS AGGroupName,
      
AR.replica_server_name         AS InstanceName,
      
HARS.role_desc,
      
Db_name(DRS.database_id)       AS DBName,
      
DRS.database_id,
      
AR.availability_mode_desc      AS SyncMode,
      
DRS.synchronization_state_desc AS SyncState,
      
DRS.last_hardened_lsn,
      
DRS.end_of_log_lsn,
      
DRS.last_redone_lsn,
      
DRS.last_hardened_time,
      
DRS.last_redone_time,
      
DRS.log_send_queue_size,
      
DRS.redo_queue_size
FROM   sys.dm_hadr_database_replica_states DRS
LEFT JOIN sys.availability_replicas AR
ON DRS.replica_id = AR.replica_id
LEFT JOIN sys.availability_groups AGS
ON AR.group_id = AGS.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
AND AR.replica_id = HARS.replica_id
ORDER  BY AGS.name,
         
AR.replica_server_name,
         
Db_name(DRS.database_id) 

Remember to run the above query on your Primary server. I have placed the output in two images for your reference.

The above columns give us an indication to our setup. What is the availability group, what are the servers, their roles, DB involved, their modes and what is their current state.

The second image which is above is the critical and the most important columns include when was the last_hardened_time and the last_redone_time. This shows how far away is my secondary. Also if the end_of_log_lsn of my Primary is different from the last_redone_lsn you know our secondary is behind my primary. For the above setup I have stopped to data sync to get this effect. Having said that, you get the drift how powerful this DMV can be.

This post is inspired from Book content: SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups” (Paperback, Kindle).

Continue reading...