casinos

Posts Tagged ‘Performance’

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...


 

SQL Server AlwaysOn-Find my Primary October 23rd, 2013

Vinod Kumar

This week is full of useful scripts that I thought is worth sharing. The more I talk with customers the more there is to talk about AlwaysOn. As the basics, AlwaysOn technologies is an scale out technology when working with SQL Server. This scale out is for Read Only workloads and it is important to understand this fundamental difference. In SQL Server 2012 we can have upto 5 replica’s and in SQL Server 2014 we can have as many as 8 replica’s in an AlwaysOn deployment.

As in many customer interactions, one of the common and simple question asked is – what is my current Primary replica NOW? Because I have set up Automatic failover etc and I am using listener to connect and I am not aware about my Primary. So here is a simple query with DMVs that will get you the required information.

SELECT AGS.name             AS AGName,

       HAGS.primary_replica AS PrimaryInstance

FROM   sys.dm_hadr_availability_group_states HAGS

INNER JOIN sys.availability_groups AGS

ON HAGS.group_id = AGS.group_id

This is a very basic query but the most useful query to start. For most cases, running queries on the Primary gives us more information than when run on secondary. More of this will be discussed in future posts.

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...


 

SSRS tuning Query–Part II October 22nd, 2013

Vinod Kumar

This is in continuation to SSRS tuning Query–Part I blog post. In this blog post I will bring couple more queries based on ExecutionLog2 View inside Reporting services Database. The first query is to find out the highest average RowCount per report execution. This is a report execution statistics.

SELECT TOP 10 instancename,
reportpath,
Avg(Datediff(s, timestart, timeend)) AS AvgReportDuration_seconds,
Avg(bytecount)                       AS AvgByteCount,
Avg([rowcount])                      AS AvgRowCount
FROM   executionlog2
WHERE  source = ‘Live’
GROUP  BY instancename,
                    
reportpath
ORDER  BY avgrowcount DESC 

In production environments, we want to find out which of the reports failed in their execution? The following query gives us the number of times a report execution failed.

SELECT instancename,
      
reportpath,
      
Count(*) AS NumberOfFailures
FROM   executionlog2
WHERE  [status] <> ‘rsSuccess’
GROUP  BY instancename,
                    
reportpath 

The next query is identifying the most resource consuming report from our Reporting Services environment. This is one the most used query when looking at resource crunched environments.

SELECT
instancename,
reportpath,
Isnull(additionalinfo.value(‘(/AdditionalInfo/EstimatedMemoryUsageKB/Pagination)[1]‘,  ‘integer’), 0) + Isnull(additionalinfo.value(‘(/AdditionalInfo/EstimatedMemoryUsageKB/Rendering)[1]‘,  ‘integer’), 0) + Isnull(additionalinfo.value(‘(/AdditionalInfo/EstimatedMemoryUsageKB/Processing)[1]‘,  ‘integer’), 0) AS EstimatedMemoryUsageKB,
Isnull(additionalinfo.value(‘(/AdditionalInfo/ScalabilityTime/Pagination)[1]‘,
‘integer’), 0) +  Isnull(additionalinfo.value(‘(/AdditionalInfo/ScalabilityTime/Rendering)[1]‘,
‘integer’), 0) +  Isnull(additionalinfo.value(‘(/AdditionalInfo/ScalabilityTime/Processing)[1]‘,
‘integer’), 0) AS ScalabilityTime_ms
FROM   executionlog2
WHERE  reportaction = ‘Render’
      
AND format = ‘RPL’
ORDER  BY estimatedmemoryusagekb DESC,
         
scalabilitytime_ms DESC 

We will keep sharing many more of such queries that I use in day-today at my work in future too. Do let me know if you would like these kind of blog posts for future too.

Continue reading...


 

SSRS tuning Query–Part I October 21st, 2013

Vinod Kumar

I have a number of these queries that I use as part of my performance tuning exercise at MTC. Out of those queries that I run, here are couple of them that I use to troubleshoot issues when working with SSRS performance.

– Query to fetch the reports that were executed against the report server

SELECT (C.path + C.name) AS ReportName,

    ES.username,

    ES.parameters,

    ES.timestart,

    ES.timeend,

    ES.timedataretrieval,

    ES.timerendering,

    ES.timeprocessing,

    ES.[rowcount],

    ES.additionalinfo

FROM   Executionlogstorage ES

INNER JOIN dbo.Catalog C

ON C.itemid = ES.reportid

And a small extension to the first query is the below query which gives a finite top-10 list to start out tuning activity from. Do you have similar

– Query to fetch the TOP 10 reports that took the longest to execute

SELECT TOP 10 (C.path + C.name) AS ReportName,

    ES.username,

    ES.parameters,

    Datediff(s, timestart, timeend) AS ReportExecutionDuration_Seconds,

    ES.timestart,

    ES.timeend,

    ES.timedataretrieval,

    ES.timerendering,

    ES.timeprocessing,

    ES.[rowcount],

    ES.additionalinfo

FROM   Executionlogstorage ES

INNER JOIN dbo.Catalog C

ON C.itemid = ES.reportid

ORDER  BY reportexecutionduration_seconds DESC

The second query is something I use almost every single time – fundamentally this query gives us the answer to “Where to start?” question. Each of these give us idea of Execution Duration, Rendering time, Processing time and these are very important values to look out for.

Continue reading...


 

Excel Tip: Undo Shortcuts October 8th, 2013

Vinod Kumar

One of the most used productivity software is Excel. It has always been that way if you are a Data person. Whenever I talk with customers who use reporting or are part of finance operations, their first choice for data manipulation defaults to Excel. There is familiarity of use, predictable and known UI, ease of formula use and many more. So in this blog, let me try to see how much we know of our tool that we use day-in-day-out a.k.a Excel. I have been writing about various Excel tips over my blog and a lot of times people ask some interesting trivia’s that I get to learn from. This post here is inspired by one such question that did come my way and I am happy to share with you.

If I were to ask – “What is the most commonly used shortcut when you are working with Office?”. What is your answer to this question? There are a number of shortcuts, but the survival of all the computer professionals will go for a toss if today’s keyboards missed “CTRL+C” and “CTRL+V”. This is one such shortcut without which our kid’s homework will be complete, our college assignments would become incomplete, and the code that we write would be incomplete and so on. Hands-down this is one of the most commonly used shortcut we would have used in almost any Office product suite – let alone Excel. Lesser known are thousands more shortcuts like these that we can use. In exploration to one such shortcut is our quiz used in later part of this post.

Undo Functionality

So if that was not the quiz, what is the quiz or tip all about anyways? The question now to you is, what are the ways you Undo the operations that you have done inside Excel. There are multiple answers:

Option 1: CTRL+Z

The most widely used shortcut to undo the series of operations is the CTRL+Z key. So we have given you one of the common answers.

Option 2: Using Quick Access Toolbar

This is one of those hidden or must say the second most used method of performing the Undo operation. Very powerful because the dropdown in that arrow shows a history and we can undo to a point in time of our document. This is more than powerful than you think.

Quiz Question

The quiz question is simple. Can you get me a 3rd Option to do the Undo operation inside Excel? This needs to be repeatable just like our CTRL+Z and I must be able to use this shortcut / operation / click and achieve the same result?

Continue reading...