-- 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
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=""
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?
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
|
|