Слайдшоу и панорамы с походов

Фотоотчет с похода на Поднебесные Зубья

Панорамы с Зубьев и не только (есть и Германия :-) )

С Днем рожденья, Ленуська!

С Днем рожденья, дочка!

SQL-полезное (SQL Server Performance and +)

Glenn Berry's SQL Server Performance

-- Hardware information from SQL Server 2008
-- (Cannot distinguish between HT and multi-core)
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)], sqlserver_start_time
FROM sys.dm_os_sys_info;


-- Get configuration values for instance
SELECT name, value, value_in_use, [description]
FROM sys.configurations
ORDER BY name;

-- Focus on
-- backup compression default
-- clr enabled (only enable if it is needed)
-- lightweight pooling (should be zero)
-- max degree of parallelism
-- max server memory (MB) (set to an appropriate value)
-- optimize for ad hoc workloads (should be 1)
-- priority boost (should be zero)


-- File Names and Paths for TempDB and all user databases in instance
SELECT DB_NAME([database_id])AS [Database Name],
[file_id], name, physical_name, type_desc, state_desc,
CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files
WHERE [database_id] > 4
AND [database_id] <> 32767
OR [database_id] = 2
ORDER BY DB_NAME([database_id]);

-- Things to look at:
-- Are data files and log files on different drives?
-- Is everything on the C: drive?
-- Is TempDB on dedicated drives?
-- Are there multiple data files?



-- Calculates average stalls per read, per write, and per total input/output for each database file.
SELECT DB_NAME(fs.database_id) AS [Database Name], mf.physical_name, io_stall_read_ms, num_of_reads,
CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms,
num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms],
io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io],
CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1))
AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(null,null) AS fs
INNER JOIN sys.master_files AS mf
ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY avg_io_stall_ms DESC;

-- Helps determine which database files on the entire instance have the most I/O bottlenecks



-- Recovery model, log reuse wait description, log file size, log usage size
-- and compatibility level for all databases on instance
SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model],
db.log_reuse_wait_desc AS [Log Reuse Wait Description],
ls.cntr_value AS [Log Size (KB)], lu.cntr_value AS [Log Used (KB)],
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %],
db.[compatibility_level] AS [DB Compatibility Level],
db.page_verify_option_desc AS [Page Verify Option], db.is_auto_update_stats_on,
db.is_auto_update_stats_async_on, db.is_parameterization_forced,
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on
FROM sys.databases AS db
INNER JOIN sys.dm_os_performance_counters AS lu
ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls
ON db.name = ls.instance_name
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%'
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ls.cntr_value > 0;

-- Things to look at:
-- How many databases are on the instance?
-- What recovery models are they using?
-- What is the log reuse wait description?
-- How full are the transaction logs ?
-- What compatibility level are they on?


-- Clear Wait Stats
-- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct HAVING SUM(W2.pct) - W1.pct < 99; -- percentage threshold -- Common Significant Wait types with BOL explanations -- *** Network Related Waits *** -- ASYNC_NETWORK_IO Occurs on network writes when the task is blocked behind the network -- *** Locking Waits *** -- LCK_M_IX Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock -- LCK_M_IU Occurs when a task is waiting to acquire an Intent Update (IU) lock -- LCK_M_S Occurs when a task is waiting to acquire a Shared lock -- *** I/O Related Waits *** -- ASYNC_IO_COMPLETION Occurs when a task is waiting for I/Os to finish -- IO_COMPLETION Occurs while waiting for I/O operations to complete. -- This wait type generally represents non-data page I/Os. Data page I/O completion waits appear -- as PAGEIOLATCH_* waits -- PAGEIOLATCH_SH Occurs when a task is waiting on a latch for a buffer that is in an I/O request. -- The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem. -- PAGEIOLATCH_EX Occurs when a task is waiting on a latch for a buffer that is in an I/O request. -- The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem. -- WRITELOG Occurs while waiting for a log flush to complete. -- Common operations that cause log flushes are checkpoints and transaction commits. -- PAGELATCH_EX Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. -- The latch request is in Exclusive mode. -- BACKUPIO Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data -- *** CPU Related Waits *** -- SOS_SCHEDULER_YIELD Occurs when a task voluntarily yields the scheduler for other tasks to execute. -- During this wait the task is waiting for its quantum to be renewed. -- THREADPOOL Occurs when a task is waiting for a worker to run on. -- This can indicate that the maximum worker setting is too low, or that batch executions are taking -- unusually long, thus reducing the number of workers available to satisfy other batches. -- CX_PACKET Occurs when trying to synchronize the query processor exchange iterator -- You may consider lowering the degree of parallelism if contention on this wait type becomes a problem -- Signal Waits for instance SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits], CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%resource waits] FROM sys.dm_os_wait_stats; -- Signal Waits above 10-15% is usually a sign of CPU pressure -- Get CPU Utilization History for last 144 minutes (in one minute intervals) -- This version works with SQL Server 2008 and SQL Server 2008 R2 only DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info); SELECT TOP(144) SQLProcessUtilization AS [SQL Server Process CPU Utilization], SystemIdle AS [System Idle Process], 100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization], DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] FROM ( SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle], record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization], [timestamp] FROM ( SELECT [timestamp], CONVERT(xml, record) AS [record] FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE N'%%') AS x
) AS y
ORDER BY record_id DESC;


-- Good basic information about memory amounts and state
SELECT total_physical_memory_kb, available_physical_memory_kb,
total_page_file_kb, available_page_file_kb,
system_memory_state_desc
FROM sys.dm_os_sys_memory;

-- You want to see "Available physical memory is high"


-- SQL Server Process Address space info
--(shows whether locked pages is enabled, among other things)
SELECT physical_memory_in_use_kb,locked_page_allocations_kb,
page_fault_count, memory_utilization_percentage,
available_commit_limit_kb, process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory;

-- You want to see 0 for process_physical_memory_low
-- You want to see 0 for process_virtual_memory_low


-- Page Life Expectancy (PLE) value for default instance
SELECT cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = N'SQLServer:Buffer Manager' -- Modify this if you have named instances
AND counter_name = N'Page life expectancy';

-- PLE is a good measurement of memory pressure.
-- Higher PLE is better. Below 300 is generally bad.
-- Watch the trend, not the absolute value.


-- Buffer cache hit ratio for default instance
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 AS [Buffer Cache Hit Ratio]
FROM sys.dm_os_performance_counters AS a
INNER JOIN (SELECT cntr_value, [OBJECT_NAME], instance_name
FROM sys.dm_os_performance_counters
WHERE counter_name = N'Buffer cache hit ratio base'
AND [OBJECT_NAME] = N'SQLServer:Buffer Manager') AS b -- Modify this if you have named instances
ON a.[OBJECT_NAME] = b.[OBJECT_NAME]
AND a.instance_name = b.instance_name
WHERE a.counter_name = N'Buffer cache hit ratio'
AND a.[OBJECT_NAME] = N'SQLServer:Buffer Manager'; -- Modify this if you have named instances

-- Shows the percentage that SQL Server is finding requested data in memory
-- A higher percentage is better than a lower percentage
-- Watch the trend, not the absolute value.


-- Memory Clerk Usage for instance
-- Look for high value for CACHESTORE_SQLCP (Ad-hoc query plans)
SELECT TOP(20) [type], [name], SUM(single_pages_kb) AS [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks
GROUP BY [type], [name]
ORDER BY SUM(single_pages_kb) DESC;

-- CACHESTORE_SQLCP SQL Plans These are cached SQL statements or batches that aren't in
-- stored procedures, functions and triggers
-- CACHESTORE_OBJCP Object Plans These are compiled plans for stored procedures,
-- functions and triggers
-- CACHESTORE_PHDR Algebrizer Trees An algebrizer tree is the parsed SQL text that
-- resolves the table and column names


-- Find single-use, ad-hoc queries that are bloating the plan cache
SELECT TOP(100) [text], cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype = N'Adhoc'
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;

-- Gives you the text and size of single-use ad-hoc queries that waste space in the plan cache
-- Enabling 'optimize for ad hoc workloads' for the instance can help (SQL Server 2008 and 2008 R2 only)
-- Enabling forced parameterization for the database can help, but test first!


-- Database specific queries *****************************************************************

-- **** Switch to a user database *****
USE YourDatabaseName;
GO

-- Individual File Sizes and space available for current database
SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB], [file_id]
FROM sys.database_files;

-- Look at how large and how full the files are and where they are located
-- Make sure the transaction log is not full!!


-- Top Cached SPs By Execution Count (SQL 2008)
SELECT TOP(100) p.name AS [SP Name], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.total_worker_time AS [TotalWorkerTime],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.execution_count DESC;

-- Tells you which cached stored procedures are called the most often
-- This helps you characterize and baseline your workload


-- Top Cached SPs By Avg Elapsed Time (SQL 2008)
SELECT TOP(25) p.name AS [SP Name], qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
qs.total_elapsed_time, qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time,
GETDATE()), 0) AS [Calls/Second], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.total_worker_time AS [TotalWorkerTime], qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY avg_elapsed_time DESC;

-- This helps you find long-running cached stored procedures


-- Top Cached SPs By Total Worker time (SQL 2008). Worker time relates to CPU cost
SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC;

-- This helps you find the most expensive cached stored procedures from a CPU perspective
-- You should look at this if you see signs of CPU pressure


-- Top Cached SPs By Total Logical Reads (SQL 2008). Logical reads relate to memory pressure
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads],
qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_reads DESC;

-- This helps you find the most expensive cached stored procedures from a memory perspective
-- You should look at this if you see signs of memory pressure


-- Top Cached SPs By Total Physical Reads (SQL 2008). Physical reads relate to disk I/O pressure
SELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads AS [TotalPhysicalReads],
qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.execution_count,
qs.total_logical_reads,qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_physical_reads, qs.total_logical_reads DESC;

-- This helps you find the most expensive cached stored procedures from a read I/O perspective
-- You should look at this if you see signs of I/O pressure or of memory pressure

-- Top Cached SPs By Total Logical Writes (SQL 2008).
-- Logical writes relate to both memory and disk I/O pressure
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_writes AS [TotalLogicalWrites],
qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_writes DESC;

-- This helps you find the most expensive cached stored procedures from a write I/O perspective
-- You should look at this if you see signs of I/O pressure or of memory pressure


-- Lists the top statements by average input/output usage for the current database
SELECT TOP(50) OBJECT_NAME(qt.objectid) AS [SP Name],
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO],
SUBSTRING(qt.[text],qs.statement_start_offset/2,
(CASE
WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) AS [Query Text]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.[dbid] = DB_ID()
ORDER BY [Avg IO] DESC;

-- Helps you find the most expensive statements for I/O by SP


-- Possible Bad NC Indexes (writes > reads)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,
user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC;

-- Look for indexes with high numbers of writes and zero or very low numbers of reads
-- Consider your complete workload
-- Investigate further before dropping an index


-- Missing Indexes current database by Index Advantage
SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage],
migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID() -- Remove this to see for entire instance
ORDER BY index_advantage DESC;

-- Look at last user seek time, number of user seeks to help determine source and importance
-- SQL Server is overly eager to add included columns, so beware
-- Do not just blindly add indexes that show up from this query!!!


-- Breaks down buffers used by current database by object (table, index) in the buffer cache
SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName],
p.index_id, COUNT(*)/128 AS [Buffer size(MB)], COUNT(*) AS [BufferCount],
p.data_compression_desc AS [CompressionType]
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
ON a.container_id = p.hobt_id
WHERE b.database_id = CONVERT(int,DB_ID())
AND p.[object_id] > 100
GROUP BY p.[object_id], p.index_id, p.data_compression_desc
ORDER BY [BufferCount] DESC;

-- Tells you what tables and indexes are using the most memory in the buffer cache


-- Get Table names, row counts, and compression status for clustered index or heap
SELECT OBJECT_NAME(object_id) AS [ObjectName],
SUM(Rows) AS [RowCount], data_compression_desc AS [CompressionType]
FROM sys.partitions
WHERE index_id < 2 --ignore the partitions from the non-clustered index if any AND OBJECT_NAME(object_id) NOT LIKE 'sys%' AND OBJECT_NAME(object_id) NOT LIKE 'queue_%' AND OBJECT_NAME(object_id) NOT LIKE 'filestream_tombstone%' GROUP BY object_id, data_compression_desc ORDER BY SUM(Rows) DESC; -- Gives you an idea of table sizes, and possible data compression opportunities -- When were Statistics last updated on all indexes? SELECT o.name, i.name AS [Index Name], STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date], s.auto_created, s.no_recompute, s.user_created, st.row_count FROM sys.objects AS o WITH (NOLOCK) INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.[object_id] = i.[object_id] INNER JOIN sys.stats AS s WITH (NOLOCK) ON i.[object_id] = s.[object_id] AND i.index_id = s.stats_id INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK) ON o.[object_id] = st.[object_id] AND i.[index_id] = st.[index_id] WHERE o.[type] = 'U' ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC; -- Helps discover possible problems with out-of-date statistics -- Also gives you an idea which indexes are most active 

And also 1
Setup for Performance Testing: Clear cache, buffers
dbcc freeproccache
go
dbcc dropcleanbuffers
go
Еще про очистку кэша
Очистка процедурного кэша для отдельной базы в SQL Server
Стандартная команда DBCC FREEPROCCACHE очищает процедурный кэш без возможности какой-либо "фильтрации" именно по базе данных.
Недокументированная команда DBCC FLUSHPROCINDB(dbid)
Вместо dbid передаем идентификатор базы данных, возвращаемый функцией db_id(), саму функцию в команде dbcc использовать не получится.
Эта команда полноценно очищает процедурный кэш от любых запросов, относящихся к конкретной базе данных.


Show size of indexes
Version 1 (problem with clustered index?, see version 2 below)
select
OBJECT_NAME(i.[object_id]) AS Table_Name,
i.index_id,
i.name,
p.partition_number,
p.rows as [#Records],
a.total_pages * 8 as [Reserved(kb)],
a.used_pages * 8 as [Used(kb)]
from
sys.indexes as i
inner join
sys.partitions as p
on i.object_id = p.object_id
and i.index_id = p.index_id
inner join
sys.allocation_units as a
on p.partition_id = a.container_id
where
i.[object_id] = object_id('table_name') --change table's name here
and i.index_id <> 1 --non clustered
order by
p.partition_number

Version 2
DECLARE
@objname nvarchar(776),
@id int,
@dbname sysname

SELECT
@objname = 'db.owner.table',
@dbname = ISNULL(parsename(@objname, 3),DB_NAME()),
@id = object_id(@objname)

SELECT
i.*,
CASE
WHEN ps.usedpages > ps.pages THEN (ps.usedpages - ps.pages)
ELSE 0
END * 8 indexsize
FROM sys.indexes i
INNER JOIN (
SELECT
OBJECT_ID,
index_id,
SUM (used_page_count) usedpages,
SUM (
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE lob_used_page_count + row_overflow_used_page_count END )pages FROM sys.dm_db_partition_stats WHERE object_id = @id GROUP BY object_id, index_id ) ps on i.index_id = ps.index_id WHERE i.object_id = @id

And also 2

How to Identify SQL Server CPU Bottlenecks
How to Identify Microsoft SQL Server Memory Bottlenecks
How to Identify I/O Bottlenecks in MS SQL Server

And Also 3
Анализ тяжелых запросов -

set transaction isolation level read uncommitted
select
 top 100
 creation_time,
 last_execution_time,
 execution_count,
 total_worker_time/1000 as CPU,
 convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
 qs.total_elapsed_time/1000 as TotDuration,
 convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
 total_logical_reads as [Reads],
 total_logical_writes as [Writes],
 total_logical_reads+total_logical_writes as [AggIO],
 convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0))as [AvgIO],
 case
 when sql_handle IS NULL then ' '
 else(substring(st.text,(qs.statement_start_offset+2)/2,(
 case
 when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2
 else qs.statement_end_offset
 end - qs.statement_start_offset)/2 ))
 end as query_text,
 db_name(st.dbid)as database_name,
 object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as object_name
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where total_logical_reads > 0
order by AvgDur desc

And Also 4
-- Missing Index Script
-- Original Author: Pinal Dave (C) 2011SELECT TOP 25
dm_mid.database_id AS DatabaseID,dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans)Avg_Estimated_Impact,dm_migs.last_user_seek AS Last_User_Seek,OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) +'_'+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +CASEWHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columnsIS NOT NULL THEN '_'ELSE ''END+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'+ ' ON ' + dm_mid.statement+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL ANDdm_mid.inequality_columns IS NOT NULL THEN ',' ELSE'' END+ ISNULL (dm_mid.inequality_columns, '')
+ ')'+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') ASCreate_StatementFROM sys.dm_db_missing_index_groups dm_migINNER JOIN sys.dm_db_missing_index_group_stats dm_migsON dm_migs.group_handle = dm_mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details dm_midON dm_mig.index_handle = dm_mid.index_handleWHERE dm_mid.database_ID = DB_ID()ORDER BY Avg_Estimated_Impact DESCGO

-- Unused Index Script
-- Original Author: Pinal Dave (C) 2011SELECT TOP 25
o.name AS ObjectName, i.name AS IndexName, i.index_id AS IndexID, dm_ius.user_seeks AS UserSeek, dm_ius.user_scans AS UserScans, dm_ius.user_lookups AS UserLookups, dm_ius.user_updates AS UserUpdates, p.TableRows, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.' +QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'FROM sys.dm_db_index_usage_stats dm_iusINNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id ANDdm_ius.OBJECT_ID = i.OBJECT_IDINNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_IDINNER JOIN sys.schemas s ON o.schema_id = s.schema_idINNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_IDFROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) pON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_IDWHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'AND i.is_primary_key = 0AND i.is_unique_constraint = 0ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups)ASCGO
And Also 5  (реально must have!)
sp_BLITZ™ – SQL Server Takeover Script

SQL Server Maintenance Solution

Интересные возможности C# и ASP.NET

Интересные возможности C# и ASP.NET

Золотая долина

 

 

 

 

ICQ, Google Talk и другие сервисы мгновенных сообщений в Google Chrome

Встраиваем ICQ, Google Talk и другие сервисы мгновенных сообщений в Google Chrome

Пробую!

SQL-полезное (Передача списка/коллекции/множества в хранимую процедуру)

Полезная статья, а также комментарии к ней
продолжение с дополнительными способами и тестированием
через xml
использование табличного типа - описание с картинками

победитель - Табличный тип (НО! только для 2008)
В БД объявляется user defined type в виде таблицы с нужным типом данных. Его можно передавать в хранимую процедуру через объект System.Data.DataTable. В хранимой процедуре этот тип будет виден как t-sql таблица, к которой можно делать запросы.

В MSSQL2008
if object_id('FindItems') is not null
drop proc FindItems
go

if exists(select * from sys.types where name = 'Identifiers')
drop type Identifiers
go

create type Identifiers AS TABLE
( id int primary key);
go

set ansi_nulls on
go
set quoted_identifier on
go

create proc FindItems
(
@categories Identifiers readonly
)
as
begin
select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName
from Item i
inner join Firm f on i.FirmId = f.FirmId
inner join Category c on i.CategoryId = c.CategoryId
inner join @categories cf on c.CategoryId = cf.Id
end
go

Вызов из С#
List categories = new List() { 1, 2, 3 };

DataTable tbCategories = new DataTable("FilterCategory");
tbCategories.Columns.Add("Id", typeof(int));
categories.ForEach(x => tbCategories.Rows.Add(x));

DataTable table = new DataTable();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=TableParameters;Integrated Security=SSPI;"))
{
connection.Open();
using (SqlCommand command = new SqlCommand("FindItems", connection) { CommandType = CommandType.StoredProcedure })
{
command.Parameters.AddWithValue("@categories", tbCategories);
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
{
dataAdapter.Fill(table);
}
}
}

Вызов из базы
declare @categories Identifiers

insert into @categories ( id ) values ( 1 )
insert into @categories ( id ) values ( 2 )
insert into @categories ( id ) values ( 3 )
insert into @categories ( id ) values ( 4 )

exec FindItems @categories

WPF - Проверка сети и "Update UI from different Thread"

Начал изучать WPF - проект в котором нестабильно работает inet
Надо проверять наличие его - отрисовываем текущий статус и подписываемся на смену статуса
В прямом вызове проблем нет, но вот из подписчика надо "Update UI from different Thread"
потому вот такой "хитрый" код в ShowInetStatus

public MainWindow()
{
InitializeComponent();

ShowInetStatus(ConnectionExists());
System.Net.NetworkInformation.NetworkChange.NetworkAvailabilityChanged +=new NetworkAvailabilityChangedEventHandler(NetworkChange_NetworkAvailabilityChanged);
}

bool ConnectionExists()
{
return System.Net.NetworkInformation.NetworkInterface.GetIsNetworkAvailable();
}
void ShowInetStatus(bool p)
{
Label theLabel = label1 as Label;
if (theLabel != null)
{
// Checking if this thread has access to the object.
if (theLabel.Dispatcher.CheckAccess())
{
// This thread has access so it can update the UI thread.
if (p)
theLabel.Content = "inet working";
else
theLabel.Content = "host not reachable.";
}
else
{
// This thread does not have access to the UI thread.
// Place the update method on the Dispatcher of the UI thread.
theLabel.Dispatcher.BeginInvoke(DispatcherPriority.Normal,
(Action)(() =>
{
if (p)
theLabel.Content = "inet working";
else
theLabel.Content = "host not reachable.";
}
));
}
}
}


Кроме того

Sacha Barber proposed a fantastic extension method that you might want to look into. It's called InvokeIfRequired, and looks a bit like this:
public static void InvokeIfRequired(this DispatcherControl control, Action operation)
{
if (control.Dispatcher.CheckAccess())
{
operation();
}
else
{
control.Dispatcher.BeginInvoke(DispatcherPriority.Normal, operation);
}
}
Then, it's simple to do:
Dispatcher.CurrentDispatcher.InvokeIfRequired(()=>{ theButton.Content="Hello"; });

SQL-полезное (change collation sql server 2008)

Сегодня ставил базу mssql2008r2ee и оставил дефаултную кодировку
потом пришлось менять

change collation sql server 2008
I have always used the below command. This has to be done after inserting the setup disk. Hope this works for you.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName
/SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ]
/SQLCOLLATION=SQL_Latin_General_CP1_CI_AS

при этом слетают логины, надо перевводить заново

SQL-полезное (Дата прописью, Сумма прописью)

http://www.t-sql.ru/post/DateWord.aspx

--Создание функции
CREATE FUNCTION DBO.GetDateWord
(
@dt DATETIME
)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @str NVARCHAR(100)

SELECT
@str=CASE DATEPART(dd, @dt)
WHEN 1 THEN 'Первого '
WHEN 2 THEN 'Второго '
WHEN 3 THEN 'Третьего '
WHEN 4 THEN 'Четвертого '
WHEN 5 THEN 'Пятого '
WHEN 6 THEN 'Шестого '
WHEN 7 THEN 'Седьмого '
WHEN 8 THEN 'Восьмого '
WHEN 9 THEN 'Девятого '
WHEN 10 THEN 'Десятого '
WHEN 11 THEN 'Одиннадцатого '
WHEN 12 THEN 'Двенадцатого '
WHEN 13 THEN 'Тринадцатого '
WHEN 14 THEN 'Четырнадцатого '
WHEN 15 THEN 'Пятнадцатого '
WHEN 16 THEN 'Шестнадцатого '
WHEN 17 THEN 'Семнадцатого '
WHEN 18 THEN 'Восемнадцатого '
WHEN 19 THEN 'Девятнадцатого '
WHEN 20 THEN 'Двадцатого '
WHEN 21 THEN 'Двадцать первого '
WHEN 22 THEN 'Двадцать второго '
WHEN 23 THEN 'Двадцать третьего '
WHEN 24 THEN 'Двадцать четвертого '
WHEN 25 THEN 'Двадцать пятого '
WHEN 26 THEN 'Двадцать шестого '
WHEN 27 THEN 'Двадцать седьмого '
WHEN 28 THEN 'Двадцать восьмого '
WHEN 29 THEN 'Двадцать девятого '
WHEN 30 THEN 'Тридцатого '
WHEN 31 THEN 'Тридцать первого '
END
+
CASE DATEPART(mm, @dt)
WHEN 1 THEN 'января'
WHEN 2 THEN 'февраля'
WHEN 3 THEN 'марта'
WHEN 4 THEN 'апреля'
WHEN 5 THEN 'мая'
WHEN 6 THEN 'июня'
WHEN 7 THEN 'июля'
WHEN 8 THEN 'августа'
WHEN 9 THEN 'сентября'
WHEN 10 THEN 'октября'
WHEN 11 THEN 'ноября'
WHEN 12 THEN 'декабря'
END
+' '+
CASE LEFT(DATEPART(yy, @dt),2)
WHEN 19 THEN 'одна тысяча девятьсот '
WHEN 20 THEN 'две тысячи '
WHEN 21 THEN 'две тысячи сто '
END
+
CASE WHEN RIGHT(DATEPART(yy, @dt), 2) IN
(10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 30, 40, 50, 60, 70, 80, 90)
THEN
CASE RIGHT(DATEPART(yy, @dt), 2)
WHEN 10 THEN 'десятого'
WHEN 11 THEN 'одиннадцатого'
WHEN 12 THEN 'двенадцатого'
WHEN 13 THEN 'тринадцатого'
WHEN 14 THEN 'четырнадцатого'
WHEN 15 THEN 'пятнадцатого'
WHEN 16 THEN 'шестнадцатого'
WHEN 17 THEN 'семнадцатого'
WHEN 18 THEN 'восемнадцатого'
WHEN 19 THEN 'девятнадцатого'
WHEN 20 THEN 'двадцатого'
WHEN 30 THEN 'трицатого'
WHEN 40 THEN 'сорокового'
WHEN 50 THEN 'пятидесятого'
WHEN 60 THEN 'шестидесятого'
WHEN 70 THEN 'семидесятого'
WHEN 80 THEN 'восьмидесятого'
WHEN 90 THEN 'девяностого'
END

ELSE

CASE RIGHT (DATEPART(yy, @dt),2)/10
WHEN 2 THEN 'двадцать'
WHEN 3 THEN 'тридцать'
WHEN 4 THEN 'сорок'
WHEN 5 THEN 'пятьдесят'
WHEN 6 THEN 'шестьдесят'
WHEN 7 THEN 'семьдесят'
WHEN 8 THEN 'восемьдесят'
WHEN 9 THEN 'девяносто'
END
+
CASE RIGHT (DATEPART(yy, @dt),1)
WHEN 1 THEN 'первого'
WHEN 2 THEN 'второго'
WHEN 3 THEN 'третьего'
WHEN 4 THEN 'четвертого'
WHEN 5 THEN 'пятого'
WHEN 6 THEN 'шестого'
WHEN 7 THEN 'седьмого'
WHEN 8 THEN 'восьмого'
WHEN 9 THEN 'девятого'
END

END
+' года'


RETURN @str

END


также Набор функций для создания суммы прописью

Наборы полезных утилит и библиотек

Наборы утилит и библиотек, которые не обязательно надо использовать, но очень сильно рекоммендуется изучить и попробовать
(в том числе и для NET)

Scott Hanselman's 2009 Ultimate Developer and Power Users Tool List for Windows

!интересно LINQPad

еще список от одного из разработчиков

сюда же Products and Extensions for Visual Studio (Visual Studio Gallery, много платных)

SQL-полезное (еще раз про постраничную навигацию или paging)

Как достичь быстрого постраничного вывода и сортировки в Вашем ASP.NET приложении

основано на статье Скотта Митчелла, “Efficiently Paging Through Large Amounts of Data”

коротко - получение страницы используя ROW_NUMBER()

CREATE PROCEDURE dbo.GetProductsPaged
(
@startRowIndex int,
@maximumRows int
)
AS
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
CategoryName, SupplierName
FROM
(
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
(SELECT CategoryName
FROM Categories
WHERE Categories.CategoryID = Products.CategoryID) AS CategoryName,
(SELECT CompanyName
FROM Suppliers
WHERE Suppliers.SupplierID = Products.SupplierID) AS SupplierName,
ROW_NUMBER() OVER (ORDER BY ProductName) AS RowRank
FROM Products
) AS ProductsWithRowNumbers
WHERE RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)

Интересно другое, это мы сами используем в своих приложениях, но ребята из
http://www.nitrosbase.net это оформили как платный продукт FastSQLDataSource!

SQL-ссылки (работа с Web-службами через CLR 2)

Продолжение работа с Web-службами через CLR
Мой SQL Server отличный переводчик
полезно как с точки зрения использования веб-служб из mssql,
так и использование Microsoft® Translator

ASP.NET - полезное (Включение SSL только для нужных страниц сайта.)

Redirect from Http to Https in ASP.NET

SQL-ссылки (дублирующие записи)

Different strategies for removing duplicate records in SQL Server

--A more simplified and faster example
WITH CTE AS
(
SELECT ROW_NUMBER() OVER
(PARTITION BY [FirstName], [LastName], [Address]
Order BY [FirstName] DESC, [LastName] DESC, [Address] DESC )
AS RowNumber,
[FirstName], [LastName], [Address]
FROM Employee tbl )
DELETE FROM CTE Where RowNumber > 1
GO
SELECT * FROM Employee
GO

SQL-полезное (Усечение LOG файла)

SQL SERVER – Shrinking Truncate Log File – Log Full

Sometime, it looks impossible to shrink the Truncated Log file. Following code always shrinks the Truncated Log File to minimum size possible.

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

====================================================================
Another best way to truncate log file with out taking transactional backup would be,

1. Change recovery model from Full to Simple ( using Interfaces) and then change it back to Full. By doing this your log file will be truncated.

2. Run DBCC ShrinkFile ( ‘LogfileName’ , 1)

By doing this, you will definitely loose your transactional log data that was stored in log file. But this will truncate Log file with out taking any backup.
USE temp;
GO
– Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE temp
SET RECOVERY SIMPLE;
GO
– Shrink the truncated log file to 100 MB.
DBCC SHRINKFILE (temp_Log, 100);
GO
– Reset the database recovery model.
ALTER DATABASE temp
SET RECOVERY FULL;
GO

SQL-ссылки (Addon for Microsoft SQL Server Management Studio)

SSMS Tools Pack is an Add-In for Microsoft SQL Server Management Studio (Express).
It contains a few upgrades to the IDE that I thought were missing from it.

The current feature list is:
SQL Snippets
Window Connection Coloring
Query Execution History and Current Window History
Format SQL
Search Table, View or Database Data
Run one script on multiple databases
Copy execution plan bitmaps to clipboard or file
Search Results in Grid Mode or Execution Plans
Generate Insert statements from resultsets, tables or database
Regions and Debug sections
Running custom scripts from Object Explorer
CRUD stored procedure generation
New query template
General options
It is simply the best free SQL Server Management Studio Add-In on the web today!
And it's better than some non-free ones too. :)

ASP.NET - полезное (UpdateProgress с заливкой "нижнего" экрана)

Красивая отправка Ajax отправка формы
автор: gaidar
Собрался уходить домой в этот пятничный вечер, но наткнулся на письмо с вопросом о том, как закрыть форму, которая по Ajax отправляется на сервер, полупрозрачным Div.

Сделать это, просто: нужно на страничке создать Div, который будет “закрывать” нужный элемент и скрыть его, отображая лишь в момент отправки формы на сервер. Для простоты, рассмотрим пример – приложение на WebForms в котором используем серенький Div, чтобы закрыть всю страницу на время отправки формы.

Создадим сам Div внутри элемента UpdateProgress:

<asp:UpdateProgress ID="UpdateProgress1" runat="server" AssociatedUpdatePanelID="UpdatePanel1"
DynamicLayout="true">

<div id="progressDiv">
<p>
Подождите... Загружаем данные на сервер...
</p>
</div>
</ProgressTemplate>
</asp:UpdateProgress>

Определим стили для этого элемента Div:

<style type="text/css">
#progressDiv
{
top: 0%;
left: 0%;
width: 100%;
height: 100%;
background-color: gray;
opacity: 0.65;
filter: alpha(opacity=65);
position: absolute;
z-index: 1001;
text-align: center;
vertical-align: middle;
}
</style>
Собственно все, в момент обновления UpdatePanel будет отображаться содержимое UpdateProgress. Код самой формы с сокращениями:

<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server" EnablePartialRendering="true">
</asp:ScriptManager>
<asp:UpdatePanel ID="UpdatePanel1" runat="server" ChildrenAsTriggers="true" UpdateMode="Always"
RenderMode="Block">
<ContentTemplate>
…

</ContentTemplate>
</asp:UpdatePanel>
<asp:UpdateProgress ID="UpdateProgress1" runat="server" AssociatedUpdatePanelID="UpdatePanel1"
DynamicLayout="true">
<ProgressTemplate>
<div id="progressDiv">
<p>
Подождите... Загружаем данные на сервер...
</p>
</div>
</ProgressTemplate>
</asp:UpdateProgress>

SQL-ссылки (Работа с Web-службами через CLR)

Работа с Web-службами через CLR
Отправка SMS через Web-сервис (CLR)