|
|
By Nabeel Derhem, on May 19th, 2012
Presenter: Joey D’Antoni
Abstract:
Have you heard about all of the new Availability Group features in SQL 2012? Thinking about implementing a DR solution, but don’t know where to start. In this presentation you’ll learn all about the new DR features in SQL Server 2012 and see them in practice.
Bio:
Joey D’Antoni is a frequent speaker at SQL Saturday events. He is a SQL Server Architect at Comcast in West Chester, Pennsylvania with 10+ years of experience working on a wide variety of database platforms. His specialties include DR, HA and Virtualization.
Door prize: There will be a raffle for a $50 Amazon gift certificate. You do not need to register for the meeting, but if you want to enter the raffle, you must register at https://www.livemeeting.com/lrs/8000181573/Registration.aspx?pageName=gvm9m015n3p5b5dj no later than 5:00 PM EDT on May 22nd.
Live Meeting Link:
https://www.livemeeting.com/cc/8000181573/join?id=8N6ZCT&role=attend
By Shehab El-Naggar, on May 8th, 2012
Continuing to what started by the first blog of DBA Dashboard using DMV & DMF, we are going today to walk down through other Dashboard cases concerning more Waits /Overload types taking place on DB production server
Third: Waits /Overload statistics
As done previously within the 1st blog , we are going to create new repository table to buffer all DMV results inside it for further usage
1- Thereby let us create first this repository as below:
--Create buffer table
USE [msdb]
CREATE TABLE [dbo].[Waits_Statistics](
[Wait_type] [varchar](400) NULL,
[Max_Wait_Time] [decimal](18, 2) NULL,
[CPU_Wait] [decimal](18, 2) NULL,
[Time] [datetime] NULL,
[CPU_impact] [varchar](400) NULL
) ON [PRIMARY] 2- Then cache all Waits statistics coming from taking place DB Server through scheduled job run by any appropriate frequency basis like 1 minute..
But take with consider some events don’t mark for waits/overload but just it is healthy wait
resulted by some commands like Waitfor delay commands.. etc
--caching all waits statistics
insert into Waits_Statistics
([Wait_type]
,Max_Wait_Time
,[CPU_Wait]
,[Time]
,CPU_impact
)
SELECT Top 30 wait_type, ISNULL (max_wait_time_ms,0)/1000 as wait_maximum , ISNULL (signal_wait_time_ms,0)/1000 as 'CPU_Wait' ,GETDATE(),
Case when signal_wait_time_ms/wait_time_ms > 0.25 Then 'High CPU Impact'else'low CPU Impact' end as 'CPU Impact Scale'
FROM sys.dm_os_wait_stats AS ws where wait_type NOT IN ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',
'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT')AND WAIT_TIME_MS >0 order by ws.max_wait_time_ms desc–Report Connection statistics through pivort query
3- Then Figure out Wait types by a specific time intervals with indentifying the weight of CPU impact
(High or low ) to identify its CPU interrelation for each wait.
--Figure out Wait types by a specific time intervals with indentifying the weight of CPU impact (High or low )
select Wait_type, coalesce (t.[High CPU Impact],0)as 'Wait_time (sec) with High CPU Impact',coalesce (t.[low CPU Impact],0)as 'Wait_time (sec) with low CPU Impact' from
(select Wait_type, max_wait_time,CPU_impact from Waits_Statistics where Time>'2012-05-05 06:57:56.497' and Time <'2012-05-06 06:57:56.497') S pivot ( sum (max_wait_time) for CPU_impact in ([High CPU Impact],[low CPU Impact]))T 4- Hence , you could Indentify now basic kind of issues relevant to these waits so fast
--Indetifying basic kind of issues relevant to these waits
;
WITH TEMP AS
(
select TIME ,Wait_type, coalesce (t.[High CPU Impact],0)as 'High CPU Impact',coalesce (t.[low CPU Impact],0)as 'low CPU Impact' from
(select TIME ,Wait_type, max_wait_time,CPU_impact from Waits_Statistics where Time>'2012-05-05 06:57:56.497' and Time 3 OR [low CPU Impact]> 3) THEN 'Yes' else 'No' end as 'Heavy locks Issues'
, case when Wait_type like 'CX%' and ( [High CPU Impact]>3 OR [low CPU Impact]> 3) THEN 'Yes' else 'No' end as 'High CPU Utilization Issues'
, case when Wait_type like 'Page%' and ( [High CPU Impact]>3 OR [low CPU Impact]> 3) THEN 'Yes' else 'No' end as 'IO overload Issues '
, case when Wait_type like 'ASYNC_NETWORK%' and ( [High CPU Impact]>3 OR [low CPU Impact]> 3) THEN 'Yes' else 'No' end as 'Network IO Issues'
, case when Wait_type like 'Log%' and ( [High CPU Impact]>3 OR [low CPU Impact]> 3) THEN 'Yes' else 'No' end as 'Transaction log Issues'
, case when Wait_type like 'OLEDB%' and ( [High CPU Impact]>3 OR [low CPU Impact]> 3) THEN 'Yes' else 'No' end as 'Backup overload'
, case when Wait_type like 'TRACEWRITE%' and ( [High CPU Impact]>3 OR [low CPU Impact]> 3) THEN 'Yes' else 'No' end as 'Profiler/Tracer Overload Issues'
, case when Wait_type like 'SOS_SCHEDULER_YIELD%' and ( [High CPU Impact]>3 OR [low CPU Impact]> 3) THEN 'Yes' else 'No' end as 'CPU process bottleneck Issues'
, case when Wait_type like 'clr%' and ( [High CPU Impact]>3 OR [low CPU Impact]> 3) THEN 'Yes' else 'No' end as 'CLR Issuess'
, case when Wait_type like 'DBMIRROR%' and ( [High CPU Impact]>3 OR [low CPU Impact]> 3) THEN 'Yes' else 'No' end as 'DBMIRROR Issues'
from temp As we have used by last blog , we have to understand the functionality of each DMV
sys. sys.dm_os_wait_stats= DMV which is specialized to get all details of waits taking place on
DB production server
• Wait type
• Accumulated wait time
• Maximum wait time
• Signal wait time (relevant CPU Wait time)
By Nabeel Derhem, on May 7th, 2012
Presenter: Erin Stellato
Abstract:
There are many DBCC statements at the disposal of Database Administrators and Developers. Some are used for the greater good; others can wreak havoc in your system. The majority of these commands are informational, but if you’re not careful you can introduce chaos or at worst, lose data you may never find again. In this session we will explore DBCC commands that are useful for the DBA and developer, and cover when to use them and how. Expect demonstrations that provide a practical application of these commands – both documented and not – that database professionals can utilize quickly and easily.
Bio:
Erin Stellato is a Senior Database Engineer at Hyland Software, Inc. in Westlake, OH. She has over 11 years of technology experience, and has worked with SQL Server for the last eight years. Erin works with customers to provide support for problems that involve the database and system performance. She is an active member of the SQL Server community and blogs about her experiences. Her areas of interest include Internals, Maintenance strategies, Performance Tuning and High Availability.
Door prize: There will be a raffle for a $50 Amazon gift certificate. You do not need to register for the meeting, but if you want to enter the raffle, you must register at https://www.livemeeting.com/lrs/8000181573/Registration.aspx?pageName=s6798gl02wk3xg74 no later than 5:00 PM EDT on May 8th.
Live Meeting Link:
https://www.livemeeting.com/cc/8000181573/join?id=J93PGC&role=attend
By Shehab El-Naggar, on May 5th, 2012
Continuing to what started by the first blog of DBA Dashboard using DMV & DMF, we are going today to walk down through other Dashboard cases concerning more Connectivity and availability of DB Server for its relevant APP servers
Second: Statistics of connections coming from APP server
As done previously within the 1st blog , we are going to create new repository table to buffer all DMV results inside it for further usage
1- Thereby let us create first this repository as below:
--Create buffer table
USE [msdb]
CREATE TABLE [dbo].[Connections_Statistics](
[client_net_address] [nvarchar](100) NULL,
[NoOfConnections] [int] NULL,
[Time] [datetime] NULL
) ON [PRIMARY] 2- Then cache all Connections statistics coming from APP servers to DB Server through scheduled job run by any appropriate frequency basis like 1 minute..
--caching all connection statistics
use msdb
insert into Connections_Statistics
SELECT
A.client_net_address,
NoOfConnections = COUNT(*),
(SELECT MAX(A.connect_time) from sys.dm_exec_connections a )as [current_time]
FROM
sys.dm_exec_connections A
INNER JOIN sys.dm_exec_sessions B ON
A.session_id = B.session_id
GROUP BY
client_net_address order by client_net_address–Report Connection statistics through pivort query
3- Then get connection statistics coming from APP serve to DB server through pivot queries below:
use msdb
declare @source nvarchar (1000) ='[IP Address 1],[IP Address 2],[IP Address 3]'
declare @sql nvarchar (max)
set @sql =N'
select * FROM Connections_Statistics PIVOT (AVG(Noofconnections) for client_net_address IN( '+@source+') )as pivot_table order by [current_time] desc '
EXEC SP_EXECUTESQL @SQL Where a mix of pivot query with parameterized SQL statement is there to identify IP Address sources within one variable easily …
4- Hence , you initiate a new alert to check out APP server outages with DB server as below:
--Report Connection statistics through pivort query
use msdb
declare @mesage nvarchar (200)
If not exists ( select 1 FROM Connections_Statistics with (nolock)where
(datediff (N,[time], GETDATE()) <1 AND client_net_address='192.168.3.158')
or(datediff (N,[time], GETDATE()) <1 AND client_net_address='192.168.4.77')
or(datediff (N,[time], GETDATE()) <1 AND client_net_address='192.168.4.106')
)
begin
set @mesage='It was traced a total outage between all APP Servers and DB Server ...Kindly please check out network connectivity'
EXECUTE msdb.dbo.sp_notify_operator @profile_name ='Profile_Name',@name=N'Mail group',@subject=N'App Servers Outage',@body=@mesage
end Where it should be scheduled as job run the same frequency basis ( 1 minute by our case) to validate
if no connections are exists there between any of APP servers and DB server for the last 1 minute
( Recommended to be proactive enough ) then it would post a mail notification to relevant Mail groups
As we have used by last blog , we have to understand the functionality of each DMV
sys.dm_exec_connections = DMV which is specialized to get all details of connections coming from different sources describing :
• IP address source
• Session ID
• Connect time
• Used network protocol
• Encryption case
• Network packet size
• …etc
By Nabeel Derhem, on April 24th, 2012
Topic Abstract:
It’s one thing to know to look for scans instead of seeks, but if you really want to know what’s going on in an execution plan, you need to dive deep. This session will cover reading execution plans from the basics, but goes way beyond the basics to cover as much of the information available within an execution plan as you might possibly ever need. We’ll go into how to understand how to interpret parameter sniffing and spot when a join operation is not performing the way you would want. We’ll use the graphical plan and the XML behind to thoroughly explore execution plans.
Speaker:
Grant Fritchey is a SQL Server MVP with over 20 years of experience in IT, including time spent in support and development. He has worked with SQL Server since 6.0, back in 1995. He has developed in VB, VB.NET, C# and Java. He has authored books for Apress and Simple-Talk and joined Red Gate Software as a Product Evangelist in January 2011.
This meeting is sponsored by CONFIO SOFTWARE. There will be a drawing for a $50 Amazon gift card at the end of the meeting. A winner will be randomly chosen from all those in attendance for the whole webcast who provide their email address.
Meeting Details:
April 26th, 2PM ET
Click here to join
By Nabeel Derhem, on April 21st, 2012
Date: April 25, 2012
Noon Eastern time – http://www.timeanddate.com/worldclock/fixedtime.html?msg=PASS+DBA+Virtual+Chapter+Live+Meeting+event&iso=20120425T12&p1=198&ah=1
Presenter: Allen White
Abstract:
Windows Server 2008 introduced Server Core, the operating system without the Graphical User Interface. SQL Server 2012 is the first version of SQL Server to support Server Core. To gain the maximum efficiency from your servers you want to make use of Server Core and with PowerShell you can effectively manage SQL Server in that environment. This session will demonstrate how to manage SQL Server 2012 on Windows Server Core and provide scripts to help you perform the common tasks you need to perform on that platform.
Bio:
Allen is a Practice Manager for Upsearch Technology Services in Northeast Ohio. He’s spent over 35 years in IT and has been using SQL Server since 1992 and is certified MCITP in SQL Server and MCT. Allen has been awarded Microsoft’s MVP Award for the last five years. He’s President of the Ohio North SQL Server User’s Group and maintains a blog at http://sqlblog.com/blogs/allen_white/default.aspx.
Door prize: There will be a raffle for a $50 Amazon gift certificate. You do not need to register for the meeting, but if you want to enter the raffle, you must register at https://www.livemeeting.com/lrs/8000181573/Registration.aspx?pageName=6v1tdp7k5tqdv31c no later than 5:00 PM EDT on April 24th.
Live Meeting Link:
https://www.livemeeting.com/cc/8000181573/join?id=PJ72R9&role=attend
Note: the meeting will be recorded and available on the meeting archives page at dba.sqlpass.org about a week after the event.
By Nabeel Derhem, on April 7th, 2012
SQLServerPath is happy to announce an Online Event for the SQL Server community. The event will take place in 16, April, 2012 starting from 4 PM GMT. You can check your local timing by clicking here.
It’s online, free, and as you can see from the poster you’ll have four great sessions.
If you are interested in attending, please REGISTER NOW !!!
By Shehab El-Naggar, on March 31st, 2012
Indeed, it is a daily question for any DBA , DB Analyst how I could demonstrate different DBA reports in a good dashboard without any third party tools or reporting skills or even availability of any crystal report tools…?!
It is doable using a mix of T-SQL Queries composed basically of :
• (Dynamic Management Queries ) queries
• DMF (dynamic Management functions)
• Pivot commands for pivot queries
• Case When Commands for statistical queries
Hence, we are going by this new series to walk down through a lot of samples of DBA reports and how we could expose + demonstrate them by the best way , in the shortest time and with the minimal efforts..
First: Details & Statistics of Expensive Queries
First , we have to admit that DMV hasn’t enough cache for all historical performance details particularly more after SQL Server Restart or DB cluster failover …
Therefore, we have to create specific jobs to warehouse all DMV results in specific repository that could be stored in msdb DB, so let us create first this repository as below:
--Create repository table
USE [msdb]
GO
CREATE TABLE [dbo].[Exp_Table](
[query_text] [varchar](4000) NULL,
[Execution_time] [date] NULL,
[last_elapsed_time] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Exp_Table_SP](
[query_text_SP] [varchar](4000) NULL,
[Execution_time] [date] NULL,
[last_elapsed_time] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Exp_Table_App](
[query_text] [varchar](4000) NULL,
[Execution_time] [date] NULL,
[last_elapsed_time] [int] NULL,
[APP_Server] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO Then caching all Expensive queries > 3 sec
--caching expensive queries >3 sec
insert into msdb.dbo.Exp_Table
SELECT
(SELECT text FROM sys.dm_exec_sql_text(qs.sql_handle)) AS query_text,qs.last_execution_time, qs.last_elapsed_time/1000000
FROM sys.dm_exec_query_stats AS qs WHERE last_elapsed_time >3000000
and qs.last_execution_time not in (select time from [msdb].[dbo].[Exp_Table])order by last_elapsed_time desc Then caching all Expensive SPs > 3 sec :
--caching expensive SPs >3 sec
insert into msdb.dbo.Exp_Table_SP
SELECT
(SELECT text FROM sys.dm_exec_sql_text(qs.sql_handle)) AS query_text,qs.last_execution_time, qs.last_elapsed_time/1000000
FROM sys.dm_exec_procedure_stats AS qs WHERE last_elapsed_time >3000000
and qs.last_execution_time not in (select time from [msdb].[dbo].[Exp_Table])order by last_elapsed_time desc Then caching all expensive queries > 3 sec but with identifying APP server coming from them for each one
--Caching all expensive queries > 3 sec but with identifying APP server
insert into msdb.dbo.Exp_Table_App
SELECT
(SELECT text FROM sys.dm_exec_sql_text(qs.sql_handle)) AS query_text,qs.last_execution_time, qs.last_elapsed_time/1000000,s.host_name APP_Server
FROM sys.dm_exec_query_stats AS qs inner join sys.dm_exec_requests R ON qs.sql_handle=R.sql_handle INNER JOIN sys.dm_exec_sessions s on s.session_id=r.session_id WHERE last_elapsed_time >1000000 and qs.last_execution_time not in (select time from msdb.dbo.Connections_Statistics_APPs with (nolock) )
order by last_elapsed_time desc Then getting results of cached DMV monitors about details of expensive queries at some specific time interval by union results of the first 2 tables “Exp_Tables” +”Exp_SPs”
Select * from Exp_table where time> start time and time start time and time <endtime Then getting statistics of expensive queries along with APP server at some specific time interval to help in compromising APP Server overload issues
use msdb
select 'Exp_avg(sec)'as Statistics_type, * from (SELECT AVG (Last_Elapsed_Time)as 'Exp_avg',APPSERVER FROM [Ex_table_APPs]where
time >='2012-01-24 22:00:18.840' and Time ='2012-01-24 22:00:18.840' and Time ='2012-01-24 22:00:18.840' and Time ='2012-01-2422:00:18.840' and Time <'2012-01-25 00:05:18.840' GROUP BY APPSERVER ) T pivot ( avg(Exp_count)for [APPServer] in ([App_Server1],[App_Server2],[App_Server3]) )S As from now and onwards, we aren’t going to show out DMV/DMF scripts to be copied and pasted only as we have used to do , but our main Goal is to understand these DMV/DMF to be more meaningful for further use as below:
• sys.dm_exec_Query_stats = DMV which is specialized to get Statistics about most of queries running on DB Server with showing its relevant query test
• sys.dm_exec_procedure_stats =DMV which is specialized to get Statistics about most of queries running on DB Server with showing its SP name and some interrelated data
• sys.dm_exec_sql_text=DMF which is used to convert Query handler column (Varbinary column ) to Query Text (Varchar Column) for more legibility about queries undertaken on DB server
• sys.dm_exec_sessions=DMV which is used to get all details of current session like session ID , hostname (APP Server ex..), connect time, last request time,…etc
• sys.dm_exec_requests=DMV which is used to get details about queries currently running like quey handler ,session id …etc text ,transaction id, wait type, start time ..etc
Please take with consider that all of these DMV/DMF are used already by SQL Server 2008 Activity monitors but for real time monitor that couldn’t be cached for days and that is what we are doing right now is an emulation for what Activity monitor is doing but with caching results for long days
By Nabeel Derhem, on March 26th, 2012
Date: March 28, 2012
Noon Eastern time – http://www.timeanddate.com/worldclock/fixedtime.html?msg=PASS+DBA+Virtual+Chapter+Live+Meeting&iso=20120328T12&p1=198&ah=1
Presenter: AJ Mendo
Abstract:
Are all your servers standardized across your environment? Do you manually click the next button when installing SQL Server? Do you manually create and configuring your routine maintenance? Are your SQL Agent Jobs owned by other DBAs in your group? Do you know when jobs fail? Or discover them accidentally? Do you have a standardized way to deploy updates?
We will discuss all these things and some others during this session and hopefully when we come out the other side some of this will make your life easier.
1. Attendees will learn how to setup standards for your environment and get other to agree to follow them.
2. Attendees will learn having a standard environment eases the pain of deployments for your projects and ones you support only part of the time. It will also help with trouble shooting.
3. Attendees will see how a standard environment will allow scripting of certain tasks and the set-up/configuration of new servers faster and easier.
Bio:
Over the years AJ has worked with a variety of databases and has worked for military, civilian, and federal government organizations supporting a wide variety of applications. He currently works on a team of DBAs supporting over 200 database servers of various sizes.
He is a frequent presenter at user groups (mostly CACTUSS), and has presented at SQLSaturday 97 in Austin. He is a habitual volunteer for SQL Events and loves being a part of the SQL Community.
Door prize: There will be a raffle for a $50 Amazon gift certificate. You do not need to register for the meeting, but if you want to enter the raffle, you must register at https://www.livemeeting.com/lrs/8000181573/Registration.aspx?pageName=k88g426fvnfc9g49 no later than 5:00 PM EST on March 27th.
Live Meeting Link:
https://www.livemeeting.com/cc/8000181573/join?id=T2WFT6&role=attend
Note: the meeting will be recorded and available on the meeting archives page at dba.sqlpass.org about a week after the event.
By Shehab El-Naggar, on March 11th, 2012
|
|
Recent Comments