Solved

Pivot

Posted on 2014-12-04
11
131 Views
Last Modified: 2014-12-07
Hi Experts

I need a pivot syntaxt to have the performance counters in rows from sys.dm_os_performance_counters

I attached the figure

 counters.jpg
Regards
0
Comment
Question by:shahin37
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40482549
Like this?
SELECT *
FROM (SELECT object_name, instance_name, counter_name, cntr_value
	FROM sys.dm_os_performance_counters) AS os_pc
PIVOT (SUM(cntr_value)
FOR counter_name IN ([Page life expectancy],[Buffer cache hit ratio],[Lock Waits/sec])) as pvt

Open in new window

0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40482575
There is, but you will have to be a bit more specific.

Here's the basic code:

With MyTable as (
select * from sys.dm_os_performance_counters
where [counter_name] in ('Page life expectancy','Buffer cache hit ratio','Lock Waits/sec') and [cntr_value] is not null)
, myPivot as
(select * from MyTable 
pivot(sum([cntr_value]) for [counter_name] in ([Page life expectancy],[Buffer cache hit ratio],[Lock Waits/sec])) as pvt)
Select object_name, instance_name, cntr_type, isnull([Page life expectancy],0) as [Page life expectancy], 
isnull([Buffer cache hit ratio],0) as [Buffer cache hit ratio], 
isnull([Lock Waits/sec],0) as [Lock Waits/sec] from myPivot

Open in new window


This gives you this:

object_name	instance_name	cntr_type	Page life expectancy	Buffer cache hit ratio	Lock Waits/sec
SQLServer:Buffer Manager    512	0	0
SQLServer:Buffer Manager    0	22	0
SQLServer:Buffer Node      	000                                                                                                                             	65792	512	0	0
SQLServer:Locks _Total                                                                                                                          	272696576	0	0	43
SQLServer:Locks AllocUnit                                                                                                                       	272696576	0	0	0
SQLServer:Locks Application                                                                                                                     	272696576	0	0	0
SQLServer:Locks Database                                                                                                                        	272696576	0	0	24
SQLServer:Locks Extent                                                                                                                          	272696576	0	0	0
SQLServer:Locks File                                                                                                                            	272696576	0	0	0
SQLServer:Locks HoBT                                                                                                                            	272696576	0	0	0
SQLServer:Locks Key                                                                                                                             	272696576	0	0	3
SQLServer:Locks Metadata                                                                                                                        	272696576	0	0	10
SQLServer:Locks Object                                                                                                                          	272696576	0	0	6
SQLServer:Locks OibTrackTbl                                                                                                                   	272696576	0	0	0
SQLServer:Locks Page                                                                                                                            	272696576	0	0	0
SQLServer:Locks RID                                                                                                                             	272696576	0	0	0

Open in new window


As you can see, there is no such thing as a single "Lock Waits/sec", so I have listed all of them. Equally, the Page life expectancy is in both the Buffer Manager and Buffer Node.

If all you want is the Buffer Manager and the _Total, then here is your code:

With MyTable as (
select [counter_name],[cntr_value] from sys.dm_os_performance_counters
where [counter_name] in ('Page life expectancy','Buffer cache hit ratio','Lock Waits/sec') and [cntr_value] is not null
and ([object_name] = 'SQLServer:Buffer Manager' or instance_name = '_Total'))
, myPivot as
(select * from MyTable 
pivot(sum([cntr_value]) for [counter_name] in ([Page life expectancy],[Buffer cache hit ratio],[Lock Waits/sec])) as pvt)
Select isnull([Page life expectancy],0) as [Page life expectancy], 
isnull([Buffer cache hit ratio],0) as [Buffer cache hit ratio], 
isnull([Lock Waits/sec],0) as [Lock Waits/sec] from myPivot

Open in new window


This gives:

Page life expectancy      Buffer cache hit ratio      Lock Waits/sec
591                                      92                                        43
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40482579
Sorry, Vitor - didn't see your post until afterwards.
0
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40482604
As you can see, there is no such thing as a single "Lock Waits/sec",
Phillip, which version are you running? In SQL Server 2008R2 there's in SQLServer:Locks.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40482614
Vitor, I've got :

SQLServer:Locks _Total                                                                                                                          	272696576	0	0	43
SQLServer:Locks AllocUnit                                                                                                                       	272696576	0	0	0
SQLServer:Locks Application                                                                                                                     	272696576	0	0	0
SQLServer:Locks Database                                                                                                                        	272696576	0	0	24
SQLServer:Locks Extent                                                                                                                          	272696576	0	0	0
SQLServer:Locks File                                                                                                                            	272696576	0	0	0
SQLServer:Locks HoBT                                                                                                                            	272696576	0	0	0
SQLServer:Locks Key                                                                                                                             	272696576	0	0	3
SQLServer:Locks Metadata                                                                                                                        	272696576	0	0	10
SQLServer:Locks Object                                                                                                                          	272696576	0	0	6
SQLServer:Locks OibTrackTbl                                                                                                                   	272696576	0	0	0
SQLServer:Locks Page                                                                                                                            	272696576	0	0	0
SQLServer:Locks RID  

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40482622
If shahin37 adds all that up, he'll get to double the right answer.

I'm trying to explain to shahin37 that, if you want a one row result, you have to understand that the answer is a bit more...nuanced.
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40482648
shahin37 need to add a WHERE clause for filtering the instance_name or else he won't has the right values.
And this is what it looks like in my MSSQL 2008R2 instance:
Capture.PNG
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40482658
Exactly - so it's probably lines 1, 2 and 15 he's interested in, as opposed to lines 3-14; but he needs to know about them and then decide.
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40482669
Well, if he don't need the instance name then MAX() will solve the problem:
SELECT *
FROM (SELECT object_name, counter_name, MAX(cntr_value) max_value
	FROM sys.dm_os_performance_counters
	GROUP BY object_name, counter_name) AS os_pc
PIVOT (SUM(max_value)
FOR counter_name IN ([Page life expectancy],[Buffer cache hit ratio],[Lock Waits/sec])) as pvt

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40482686
:-)
0
 

Author Comment

by:shahin37
ID: 40486074
Hi gues,

thanks for your prompt responces
I think I can use philip Syntax  by changing the sum to max .

Regards
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question