SSAStack @ssastack - Tumblr Blog

T-SQL to return disk space in a table

SET NOCOUNT ON

DECLARE @hr INT

DECLARE @fso INT

DECLARE @drive CHAR(1)

DECLARE @odrive INT

DECLARE @TotalSize VARCHAR(20)

DECLARE @MB NUMERIC ;

SET @MB = 1048576

CREATE TABLE #drives

(

drive CHAR(1) PRIMARY KEY,

FreeSpace INT NULL ,

TotalSize INT NULL

)

INSERT #drives

(

drive,

FreeSpace

)

EXEC master.dbo.xp_fixeddrives

EXEC @hr=sp_OACreate'Scripting.FileSystemObject',

@fso OUT

IF @hr <> 0

EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD FOR

SELECT drive

FROM #drives

ORDER BY drive

OPEN dcur

FETCH NEXT

FROM dcur

INTO @drive

WHILE @@FETCH_STATUS=0

BEGIN

EXEC @hr = sp_OAMethod @fso,

'GetDrive',

@odrive OUT,

@drive

IF @hr <> 0

EXEC sp_OAGetErrorInfo @fso

EXEC @hr = sp_OAGetProperty @odrive,

'TotalSize',

@TotalSize OUT

IF @hr <> 0

EXEC sp_OAGetErrorInfo @odrive

UPDATE #drives

SET TotalSize=@TotalSize/@MB

WHERE drive =@drive

FETCH NEXT

FROM dcur

INTO @drive

END

CLOSE dcur

DEALLOCATE dcur

EXEC @hr=sp_OADestroy @fso

IF @hr <> 0

EXEC sp_OAGetErrorInfo @fso

SELECT drive ,

TotalSize AS 'Total(MB)',

FreeSpace AS 'Free(MB)'

FROM #drives

ORDER BY drive

DROP TABLE #drives

#SQL #OLE automatio #Drive Space

•18+ Adults Only

Watch Anya Live on Cam

Anya is live and ready to show you everything. Watch her strip, dance, and perform exclusive shows just for you. Interact in real-time and make your fantasies come true.

✓ Live Streaming✓ Interactive Chat✓ Private Shows✓ HD Quality✓ Free Actions

Free to watch • No registration required • HD streaming

This nifty little bit of code allows you to use XML without bashing your face in with Xpath or Xquery.

Also you have no need to know what the incoming XML looks like as the function takes care of that.

Read the post .. Very cool

#XML #SQL Server

SSAS DMV's for everything

I especially like this one

select * from systemrestrictschema($system.discover_performance_counters, PERF_COUNTER_NAME = ‘\MSAS 2008:Connection\Current connections’, PERF_COUNTER_NAME = ‘\MSAS 2008:MDX\Total Cells Calculated’)

#DMV #SSAS #Performance

Deploy a BI solution via script

In this awesome blog post they author details how to create a bulletproof deployment script for your BI solution.

http://blog.boxedbits.com/archives/50

#BI #Deployment

Executing XMLA without SQL Server Agent Jobs

Declare @XMLA nvarchar(1000), @DateSerial nvarchar(35); -- Change date to format YYYYMMDDHHMMSS Set @DateSerial = Convert(nvarchar, getdate(), 112) + Replace(Convert(nvarchar, getdate(), 108), ':', ''); -- Create the XMLA string Set @XMLA = N'<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>Adventure Works DW 2008R2</DatabaseID> </Object> <File>c:\bak\Adventure Works DW 2008R2_' + @DateSerial + '.abf</File> </Backup>'; -- Execute the string across the linked server (SSAS) Exec (@XMLA) At SSAS;

#XMLA #Linked server

•18+ Adults Only

Watch Anya Live on Cam

Anya is live and ready to show you everything. Watch her strip, dance, and perform exclusive shows just for you. Interact in real-time and make your fantasies come true.

✓ Live Streaming✓ Interactive Chat✓ Private Shows✓ HD Quality✓ Free Actions

Free to watch • No registration required • HD streaming

#SSAS performance #Counters #Management Data Warehouse

This post will describe how you can create new MDW data collection sets to capture SQL Server Analysis Services (SSAS) 2008 performance counters.

How to identify the most costly SQL Server queries using DMV’s

Please read the full article here

SELECT TOP 20

qs.sql_handle,

qs.execution_count,

qs.total_worker_time AS Total_CPU,

total_CPU_inSeconds = --Converted from microseconds

qs.total_worker_time/1000000,

average_CPU_inSeconds = --Converted from microseconds

(qs.total_worker_time/1000000) / qs.execution_count,

qs.total_elapsed_time,

total_elapsed_time_inSeconds = --Converted from microseconds

qs.total_elapsed_time/1000000,

st.text,

qp.query_plan

FROM

sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp

ORDER BY qs.total_worker_time DESC

#DMV #SQL #Performance

#SQL #SQL SERVER

Total memory used by SQL Server

CREATE TABLE #tmp

( row_id INT IDENTITY PRIMARY KEY, name VARCHAR(100), value BIGINT );

INSERT INTO #tmp EXEC ( 'DBCC MEMORYSTATUS' );

SELECT top 1 value / 1024 'KBUsed'

FROM #tmp

WHERE RTRIM(LTRIM(name)) = 'Working Set';

DROP TABLE #tmp;

#SQL #SQL SERVER

•18+ Adults Only

Watch Anya Live on Cam

Anya is live and ready to show you everything. Watch her strip, dance, and perform exclusive shows just for you. Interact in real-time and make your fantasies come true.

✓ Live Streaming✓ Interactive Chat✓ Private Shows✓ HD Quality✓ Free Actions

Free to watch • No registration required • HD streaming

Deploying a Microsoft BI Solution via Script

This article goes into some detail on how to deploy via script for all the typical parts of a MS BI solution: the database, integration services packages, SQL Agent jobs and the SSAS cubes.

http://blog.boxedbits.com/archives/50

#SSAS #XMLA #DEPLOYMENT #COMMANDLINE #ASCMD

T-SQL Query Script to monitor Memory Usage of a SQL Server Instance

This is something I've needed to do on many occasions. I am sure you have too.

the post referenced has some awesome things

http://blogs.msdn.com/b/sqlsakthi/archive/2011/02/28/t-sql-script-to-monitor-memory-usage-by-sql-server-instance.aspx

#sqlserver #data #Usage #SSAS

SQL Server 2008 data collection sets

Before you actually go out and create a completely custom data collector for performance counters, you should consider something. The System Activity Collection Set that is one of the System Collection Sets, already includes over 60 performance counters as a collection item. You can see what ones are already included by running the following query:

SELECT name, frequency, parameters FROM syscollector_collection_items WHERE name = 'Server Activity - Performance Counters'

Since the definition of the collection items is XML based, you can click the XML Document and it will open in a new window with friendly formatting. If you find that the default counters don't meet your specific needs, then you can use the following example to create a custom collection set:

use msdb; --First create the collection set declare @collection_set_id int declare @collection_set_uid uniqueidentifier exec [dbo].[sp_syscollector_create_collection_set] @name=N'Performance Counter Collection Set', @collection_mode=0, --Let's start in cached mode. @description=N'Collects Performance Counters from PerfMon', @target=N'', --Undocumented @logging_level=0, --0 through 2 are valid @days_until_expiration=5, --Let's just keep data 5 days. We will rollup for reporting. @proxy_name=N'', --Use if you want it to run under something other than the SQL Agent svc account. @schedule_name=N'CollectorSchedule_Every_5min', --Built in schedule @collection_set_id=@collection_set_id output, @collection_set_uid=@collection_set_uid output -- Now create the collection item for the Performance Counters to be collected declare @collector_type_uid uniqueidentifier declare @collection_item_id int select @collector_type_uid = collector_type_uid from [dbo].[syscollector_collector_types] where name = N'Performance Counters Collector Type'; exec [dbo].[sp_syscollector_create_collection_item] @name=N'Standard SQL Server Performance Counters', @parameters=N' <ns:PerformanceCountersCollector xmlns:ns="DataCollectorType"> <PerformanceCounters Objects="Processor" Counters="% Processor time" Instances="_Total" /> <PerformanceCounters Objects="Processor" Counters="% Privileged time" Instances="_Total" /> <PerformanceCounters Objects="Memory" Counters="Available KBytes" Instances="*" /> <PerformanceCounters Objects="Memory" Counters="Pages/sec" Instances="*" /> <PerformanceCounters Objects="Memory" Counters="Committed Bytes" Instances="*" /> <PerformanceCounters Objects="Memory" Counters="Commit limit" Instances="*" /> <PerformanceCounters Objects="System" Counters="Processor Queue Length" Instances="*" /> <PerformanceCounters Objects="System" Counters="Context Switches/sec" Instances="*" /> <PerformanceCounters Objects="PhysicalDisk" Counters="Avg. Disk Queue Length" Instances="_Total" /> <PerformanceCounters Objects="PhysicalDisk" Counters="Avg. Disk sec/Read" Instances="_Total" /> <PerformanceCounters Objects="PhysicalDisk" Counters="Avg. Disk sec/Write" Instances="_Total" /> <PerformanceCounters Objects="PhysicalDisk" Counters="Disk Reads/sec" Instances="_Total" /> <PerformanceCounters Objects="PhysicalDisk" Counters="Disk Writes/sec" Instances="_Total" /> <PerformanceCounters Objects="PhysicalDisk" Counters="Disk Read Bytes/sec" Instances="_Total" /> <PerformanceCounters Objects="PhysicalDisk" Counters="Disk Write Bytes/sec" Instances="_Total" /> <PerformanceCounters Objects="Process" Counters="% Processor time" Instances="sqlservr" /> <PerformanceCounters Objects="SQLServer:Buffer Manager" Counters="Buffer Cache hit ratio" Instances="*" /> <PerformanceCounters Objects="SQLServer:Buffer Manager" Counters="Checkpoint pages/sec" Instances="*" /> <PerformanceCounters Objects="SQLServer:Buffer Manager" Counters="Page life expectancy" Instances="*" /> <PerformanceCounters Objects="SQLServer:Memory Manager" Counters="Total Server Memory (KB)" Instances="*" /> <PerformanceCounters Objects="SQLServer:Memory Manager" Counters="Target Server Memory(KB)" Instances="*" /> <PerformanceCounters Objects="SQLServer:SQL Statistics" Counters="Batch requests/sec" Instances="*" /> <PerformanceCounters Objects="SQLServer:SQL Statistics" Counters="SQL Compilations/sec" Instances="*" /> <PerformanceCounters Objects="SQLServer:SQL Statistics" Counters="SQL Re-Compilations/sec" Instances="*" /> <PerformanceCounters Objects="SQLServer:Databases" Counters="Transactions/sec" Instances="_Total" /> <PerformanceCounters Objects="SQLServer:Databases" Counters="Data File(s) Size (KB)" Instances="_Total" /> <PerformanceCounters Objects="SQLServer:General Statistics" Counters="User Connections" Instances="*" /> </ns:PerformanceCountersCollector>', @collection_item_id=@collection_item_id output, @frequency=5, @collection_set_id=@collection_set_id, --output from sp_syscollector_create_collection_set @collector_type_uid=@collector_type_uid --Start the collection exec sp_syscollector_start_collection_set @collection_set_id = @collection_set_id --output from sp_syscollector_create_collection_set

#SQL Collector dests #Data #Useful

Identify and Kill Top Blocking Processes In MS SQL Server

You can use the below sample t-sql code in order to list the top blocking processes in a MS SQL Server instance. But before executing the t-sql query, you should switch to the master sql server database.

use master GO select * from sysprocesses (nolock) where blocked = 0 and spid in ( select blocked from sysprocesses (nolock) where blocked <> 0 ) GO

After defining the user process running on the SQL Server instance, you can kill if you are required to kill that sql process by running the Kill @spid t-sql command.

#SQLServer #Useful

Trending Blogs

Last Seen Blogs

SSAStack