Sreenivas Mogullapalli

some helpful stuff for identifying which need optimization in sql server

2009-07-08T06:41:3105:30:00

-- Query to identify the missing indexes in t-sql of sql server

-- from the dynamic views

BEGIN

-- Do not lock anything, and do not get held up by any locks.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT TOP 20

st.text AS [SQL]

, cp.cacheobjtype

, cp.objtype

, DB_NAME(st.dbid)AS [DatabaseName]

, cp.usecounts AS [Plan usage]

, qp.query_plan

FROM sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

WHERE CAST(qp.query_plan AS NVARCHAR(MAX))LIKE '%<MissingIndexes>%'

ORDER BY cp.usecounts DESC

END

-- some variations of the same to find some other patterns which need optimization.

1. exec dbo.dba_SearchCachedPlans '%<MissingIndexes>%'
2. exec dbo.dba_SearchCachedPlans '%<ColumnsWithNoStatistics>%'
3. exec dbo.dba_SearchCachedPlans '%<TableScan%'
4. exec dbo.dba_SearchCachedPlans '%CREATE PROC%MessageWrite%'

-- to find Top 20 Expensive Queries in sql server

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.text)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1),

qs.execution_count,

qs.total_logical_reads, qs.last_logical_reads,

qs.min_logical_reads, qs.max_logical_reads,

qs.total_elapsed_time, qs.last_elapsed_time,

qs.min_elapsed_time, qs.max_elapsed_time,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

WHERE qt.encrypted=0

ORDER BY qs.total_logical_reads DESC

-- to find Who did What When in sql server?

select sys.dm_exec_sessions.session_id,sys.dm_exec_sessions.host_name,sys.dm_exec_sessions.program_name,

sys.dm_exec_sessions.client_interface_name,sys.dm_exec_sessions.login_name,

sys.dm_exec_sessions.nt_domain,sys.dm_exec_sessions.nt_user_name,

sys.dm_exec_connections.client_net_address,

sys.dm_exec_connections.local_net_address,sys.dm_exec_connections.connection_id,sys.dm_exec_connections.parent_connection_id,

sys.dm_exec_connections.most_recent_sql_handle,

(select text from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as sqlscript,

(select db_name(dbid) from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as databasename,

(select object_id(objectid) from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as objectname

from sys.dm_exec_sessions inner join sys.dm_exec_connections

on sys.dm_exec_connections.session_id=sys.dm_exec_sessions.session_id

these, i found on some other sites and posting here for my reference so that i don't miss them

Tip: Changing Validator text color using class name

2008-10-30T17:03:4605:30:00

In my ASP.NET application validators are used for validating the data entry part and as per the requirement we need to show a different color for error messages. We have created a CSS class for all the error message and applied to validators but noticed that the error messages are always displayed in RED color. The trick here is we also need to set the ForeColor to empty to get the color of text from CSS Class. Sample is provided below.

CssName="Someclass" ForeColor=""

Microsoft SQL Server 2008 CTP, November 2007

2008-01-04T10:14:5905:30:00

Available at http://www.microsoft.com/downloads/details.aspx?FamilyID=3bf4c5ca-b905-4ebc-8901-1d4c1d1da884&DisplayLang=en.

Windows Server 2008 Developer Training Kit

2008-01-03T15:48:1005:30:00

Microsoft released the beta 3 version of this kit at http://www.microsoft.com/downloads/details.aspx?FamilyID=b36ee81a-aff5-4314-95d7-dad3acfa8094&DisplayLang=en.

Brief Description

The Windows Server 2008 Developer Training Kit includes a set of hands-on-labs and presentations for technologies such as IIS7, Management, WCF, WF, Windows CardSpace, Windows PowerShell, Virtualization and the Transactional File System (TxF).

Potential usage problem with Silver light?

2008-01-02T15:46:0705:30:00

There is no doubt that silver light is providing lot of good presentation features and i like the technology. Recently i realized that Right Click menu popup on silver light object provides only one menu item which is "silverlight configuration'. Most of the times i use right click menu popup to open links in "new tab" or "new window" or "save the target" content but this is missing in silver light object. I feel this feature should be retained on all the hyperlinks provided in silver light object. Any one knows any alternative to this?

Silver Light enabled Microsoft Download site (beta)

2007-12-27T16:46:4605:30:00

Microsoft seem to implement silverlight on downloads site. Now it is in beta stage and available at http://www.microsoft.com/beta/downloads/Default.aspx. Previews of the same included below.

Microsoft SQL Server 2008 – Learning Portal

2007-12-27T15:27:1205:30:00

Just noticed that Microsoft SQL Server 2008 has its own learning portal on microsoft site at http://www.microsoft.com/learning/sql/2008/default.mspx. The following is some of the content to know and learn about Sql Server 2008.

Get ready for Microsoft SQL Server 2008 now.

Special Offers:

*	Free e-book offer: Get *free* excerpts from Introducing SQL Server 2008 by Peter DeBetta.

Free e-learning:

•	Collection 6187: What's New in Microsoft SQL Server 2008 (includes three *free* clinics)

Classroom training:

•	Course 6156: First Look: What's New in Microsoft SQL Server 2008

Microsoft E-Learning

This Microsoft Official E-Learning clinic leverages a rich multimedia experience to introduce you to new and enhanced features in SQL Server 2008, including new data types and data management features.

•

Collection 6187: What’s New in Microsoft SQL Server 2008 (includes three free clinics)

•	Clinic 6188: What's New in Microsoft SQL Server 2008 for Enterprise Data Platform
•	Clinic 6189: What's New in Microsoft SQL Server 2008 for Business Intelligence
•	Clinic 6190: What's New in Microsoft SQL Server 2008 for Database Development

Free e-book: Team Development with TFS Guide

2007-12-20T17:56:0705:30:00

Another free ebook from Microsoft. This time it is from Microsoft Patterns and Practices Team. All the details are provided at http://www.codeplex.com/TFSGuide. You can read this book online or you can download it in PDF format.

Free e-book on Microsoft LINQ, Microsoft ASP.NET AJAX, and Microsoft Silverlight

2007-12-19T17:25:0205:30:00

This offer is from Microsoft. Get it at http://www.microsoft.com/learning/vstudio/2008/default.mspx#EBOOK

The free e-book includes content from three recent publications from Microsoft Press:

•	Introducing Microsoft LINQ by Paolo Pialorsi and Marco Russo (ISBN: 9780735623910) This practical guide covers Language Integrated Query (LINQ) syntax fundamentals, LINQ to ADO.NET, and LINQ to XML. The e-book includes the entire contents of this printed book!
•	Introducing Microsoft ASP.NET AJAX by Dino Esposito (ISBN: 9780735624139) Learn about the February 2007 release of ASP.NET AJAX Extensions 1.0, including an overview and the control toolkit.
•	Introducing Microsoft Silverlight 1.0 by Laurence Moroney (ISBN: 9780735625396) Learn how to use Silverlight to simplify the way you implement compelling user experiences for the Web. Discover how to support an object-oriented program model with JavaScript.

Windows XP Service Pack 3 RC

2007-12-19T12:10:5905:30:00

Microsoft released Windows XP Service Pack 3 Release Candidate. This is available at http://www.microsoft.com/downloads/details.aspx?FamilyID=75ed934c-8423-4386-ad98-36b124a720aa&DisplayLang=en