Frédéric WAUQUIER

Le site personnel de Frédéric WAUQUIER

SQL – Identifies indexes that are fragmented and defragments them.

without comments

/****************************************************************************** 
1. Identifies indexes that are fragmented and defragments them. For certain 
   tables, a fill-factor is set in order to improve insert performance. 
   Based on MSDN sample at http://msdn2.microsoft.com/en-us/library/ms188917.aspx 
2. Updates potentially out-of-date table statistics. 
******************************************************************************/ 
 
SET NOCOUNT ON; 
 
-- Rebuild or reorganize indexes based on their fragmentation levels 
DECLARE @work_to_do TABLE ( 
    objectid int 
    , indexid int 
    , pagedensity float 
    , fragmentation float 
    , numrows int 
) 
 
DECLARE @objectid int; 
DECLARE @indexid int; 
DECLARE @schemaname nvarchar(130);  
DECLARE @objectname nvarchar(130);  
DECLARE @indexname nvarchar(130);  
DECLARE @numrows int 
DECLARE @density float; 
DECLARE @fragmentation float; 
DECLARE @command nvarchar(4000);  
DECLARE @fillfactorset bit 
DECLARE @numpages int 
 
-- Select indexes that need to be defragmented based on the following 
-- * Page density is low 
-- * External fragmentation is high in relation to index size 
PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121)  
INSERT @work_to_do 
SELECT 
    f.object_id 
    , index_id 
    , avg_page_space_used_in_percent 
    , avg_fragmentation_in_percent 
    , record_count 
FROM  
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f 
WHERE 
    (f.avg_page_space_used_in_percent < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count < page_count - 1) 
    or (f.page_count > 50 and f.avg_fragmentation_in_percent > 15.0) 
    or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0) 
 
PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar(20)) 
 
PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121) 

SELECT @numpages = sum(ps.used_page_count) 
FROM 
    @work_to_do AS fi 
    INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id 
    INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id 
 
-- Declare the cursor for the list of indexes to be processed. 
DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do 
 
-- Open the cursor. 
OPEN curIndexes 
 
-- Loop through the indexes 
WHILE (1=1) 
BEGIN 
    FETCH NEXT FROM curIndexes 
    INTO @objectid, @indexid, @density, @fragmentation, @numrows; 
    IF @@FETCH_STATUS < 0 BREAK; 
 
    SELECT  
        @objectname = QUOTENAME(o.name) 
        , @schemaname = QUOTENAME(s.name) 
    FROM  
        sys.objects AS o 
        INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id 
    WHERE  
        o.object_id = @objectid; 
 
    SELECT  
        @indexname = QUOTENAME(name) 
        , @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END 
    FROM  
        sys.indexes 
    WHERE 
        object_id = @objectid AND index_id = @indexid; 
 
    IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation < 30.0) 
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; 
    ELSE IF @numrows >= 5000 AND @fillfactorset = 0 
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)'; 
    ELSE 
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; 
    PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command; 
    EXEC (@command); 
    PRINT convert(nvarchar, getdate(), 121) + N' Done.'; 
END 
 
-- Close and deallocate the cursor. 
CLOSE curIndexes; 
DEALLOCATE curIndexes; 
 
 
IF EXISTS (SELECT * FROM @work_to_do) 
BEGIN 
    PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20)) 
    SELECT @numpages = @numpages - sum(ps.used_page_count) 
    FROM 
        @work_to_do AS fi 
        INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id 
        INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id 
 
    PRINT 'Estimated number of pages freed: ' + cast(@numpages as nvarchar(20)) 
END 
GO 
 
 
--Update all statistics 
PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121)  
EXEC sp_updatestats 
PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)  
GO 

Written by Frédéric WAUQUIER

janvier 5th, 2015 at 11:41

Posted in TSQL

Script to remove all SQL statistics

without comments

USE [Master];

GO
BEGIN
SET NOCOUNT ON;

-- Table to hold all auto stats and their DROP statements
declare @commands TABLE (Database_Name SYSNAME, Table_Name SYSNAME, Stats_Name SYSNAME, cmd NVARCHAR(4000));
DECLARE @Database_Name SYSNAME;
DECLARE @cmd NVARCHAR(4000);


-- A cursor to browse all user databases
DECLARE Databases CURSOR FOR SELECT [name] FROM sys.databases WHERE  database_id > 4;
OPEN Databases;

FETCH NEXT FROM Databases INTO @Database_Name;
WHILE @@FETCH_STATUS= 0 BEGIN

    -- Create all DROP statements for the database
    SET @cmd ='SELECT N''' + @Database_Name + ''',so.name,ss.name,N''DROP STATISTICS [''+ ssc.name +'']''+''.[''+ so.name+'']''+ ''.[''+ ss.name+ ''];''
                     FROM [' + @Database_Name + '].sys.stats AS ss 
				    INNER JOIN [' + @Database_Name + '].sys.objects AS so ON ss.[object_id] = so.[object_id]
				    INNER JOIN [' + @Database_Name + '].sys.schemas AS ssc ON so.schema_id = ssc.schema_id
                     WHERE ss.auto_created = 1 AND so.is_ms_shipped = 0';
    --SELECT @cmd -- DEBUG
    
    -- Execute and store in temp table
    INSERT INTO @commands
	   EXECUTE (@cmd);

    -- Next Database
   FETCH NEXT FROM Databases INTO   @Database_Name;
END;
CLOSE Databases;
DEALLOCATE Databases;

WITH Ordered_Cmd AS
-- Add an ordering column to the rows to mark database context
(
    SELECT ROW_NUMBER() OVER	  (PARTITION BY  Database_Name ORDER BY Database_Name,Table_Name,Stats_Name) AS Row_Num, * FROM   @commands
)

SELECT
-- Add the USE statement before the first row for the database
    CASE 
	   WHEN   Row_Num = 1 THEN   REPLICATE(N'-',50) + NCHAR(10) + NCHAR(13)+ N'USE [' + Database_Name + '];'+ NCHAR(10) + NCHAR(13)
        ELSE   ''
    END
    + cmd
    FROM   Ordered_Cmd
    ORDER BY Database_Name, Table_Name, Stats_Name;
END
GO 

Written by Frédéric WAUQUIER

janvier 5th, 2015 at 11:37

Posted in TSQL

Create Outlook tasks from all mails into a specific folder

without comments

This macro create a new task for each mail in a specific folder (with attachment of original mail)

Option Compare Text

Public Sub Main()

Dim objNS As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Dim Msg As Outlook.MailItem
Dim strID As String
Dim olMail As Outlook.MailItem
Dim objTask As Outlook.TaskItem
Dim regex
Dim matches, customSubject, subject

Set regex = CreateObject("vbscript.regexp")
regex.Pattern = "/wf (.*)"
regex.IgnoreCase = True
regex.Global = True

Set objNS = GetNamespace("MAPI")
Set objFolder = objNS.Folders("frederic@wauquier.net")
Set objFolder = objFolder.Folders("Boîte de réception")
Set objFolder = objFolder.Folders("To Tasks")

For Each MyMail In objFolder.Items
  If TypeName(MyMail) = "MailItem" Then
    strID = MyMail.EntryID
    Set olMail = objNS.GetItemFromID(strID)
    Set objTask = Application.CreateItem(olTaskItem)
    objTask.Attachments.Add MyMail

    Set matches = regex.Execute(olMail.Body)
    If matches.Count <> 0 Then
      customSubject = matches(0).submatches(0)
    Else
      customSubject = ""
    End If
    If customSubject <> "" Then
      subject = customSubject
    Else
      subject = olMail.subject
    End If
    With objTask
      .subject = subject
      .categories = "@NEW"
      .Body = olMail.Body
      .RTFBody = olMail.RTFBody
      .Sensitivity = olMail.Sensitivity
      .Importance = olMail.Importance
      .ContactNames = olMail.Sender.Name
      .ToDoTaskOrdinal = olMail.ToDoTaskOrdinal
    End With
    objTask.Save
    Set objTask = Nothing
    Set olMail = Nothing
  End If
Next MyMail
End Sub

Written by Frédéric WAUQUIER

mars 28th, 2014 at 2:41

Cleanup Xbmc mySql database

without comments

Remove incorrect movies

Delete FROM `movie` WHERE IdFile not in (select idFile from files);
Delete FROM `episode` WHERE IdFile not in (select idFile from files);
Delete FROM `musicvideo` WHERE IdFile not in (select idFile from files);

Remove Sets without movies

delete from sets where idset not in (select idset from movie where not idset is null);

List of sets with only one movie

select * from sets where idset in (SELECT DISTINCT idSet FROM movie WHERE not idset is null group by idset having count(*)<2);

Remove sets with less than 3 movies referred

update movie
	left join 
		(SELECT DISTINCT idSet 
        		FROM movie 
                	WHERE not idset is null 
                	group by idset 
                	having count(*)<3) as P 
                on P.idSet=movie.idSet
       	set movie.idSet=null
        where (not movie.idset is null)
        	and (not P.idSet is null);

delete from sets where idset not in (select idset from movie where not idset is null);

Full Set Optimization

update sets set strSet=trim(replace(strSet,'(',''));
update sets set strSet=trim(replace(strSet,')',''));
update sets set strSet=trim(replace(strSet,'''',' '));
update sets set strSet=trim(replace(strSet,'\\',''));
update sets set strSet=trim(replace(strSet,'Collection',''));
update sets set strSet=trim(replace(strSet,'Trilogie',''));
update sets set strSet=trim(replace(strSet,'Saga',''));

update movie
	left join sets on sets.idSet=movie.idSet
       	set movie.idSet=null
        where (not movie.idset is null) and (trim(sets.strSet)='');
        
update movie
	left join (SELECT DISTINCT idSet 
        		FROM movie 
                        WHERE not idset is null 
                        Group by idset  	
                        having count(*)<3) as P 
                on P.idSet=movie.idSet
       	set movie.idSet=null
        where (not movie.idset is null) and (not P.idSet is null);

update movie
	left join (select s1.idSet, s2.idSet AS idSetOk 
            		from sets AS s1
	 		join (select strSet,min(idSet) as idSet 
                        		from sets 
                                        group by strSet) as s2
         			on s1.strSet=s2.strSet
           		having s1.Idset<>s2.idSet) as P 
                on P.idSet=movie.idSet
       	set movie.idSet=P.idSetOk
        where (not movie.idset is null) 
        and (not P.idSet is null)
        and (movie.idSet<>P.idSetOk);
        
delete from sets where idset not in (select idset from movie where not idset is null);

select * from sets limit 0,1000;

Written by Frédéric WAUQUIER

septembre 21st, 2013 at 2:16

Posted in Non classé

Update name of Cisco Ip Phone In SpiceWorks

without comments

Update devices set name=replace(description,'Cisco IP ','')
where device_type='VoipDevice'
and description like 'Cisco IP Phone %';

select name,manufacturer,model,server_name as Address from devices
where device_type='VoipDevice'
and description like 'Cisco IP Phone %' order by Address;

Written by Frédéric WAUQUIER

août 22nd, 2013 at 11:05

Posted in SpiceWorks

List of SQL server instance in SpiceWorks

without comments

Create a new report with ‘Build this report using SQL’ checked and copy following SQL to generate report

SELECT Distinct devices.name as ComputerName
       ,Software.Name as SqlVersionInstalled
       ,Software_installations.Version
       ,microsoft_sql_servers.name as InstanceName
       ,devices.Operating_system as OS
       ,devices.number_of_processors as NumberOfProcessor
       ,devices.processor_type as ProcessorType
       ,devices.memory/(1024*1024) as Memory

       FROM microsoft_sql_servers
            JOIN devices ON microsoft_sql_servers.computer_id = devices.id
            JOIN Software_installations ON devices.id=Software_installations.computer_id
            JOIN Software on Software_installations.Software_id=software.id
            Where Software.Name like 'Microsoft SQL Server%Edition'
       Order by     devices.name,microsoft_sql_servers.name
            

Written by Frédéric WAUQUIER

juin 19th, 2013 at 11:35

Convert all audio files recursively to mp3 using ffmpeg and PowerShell

without comments

$objParent = Get-ChildItem . -Include *.aac, *.flac, *.m4p, *.ogg, *.ra, *.rm, *.ram, *.raw, *.wav, *.wma -recurse 
foreach ($child in $objParent) 
{
	$previousName= $child.FullName 
	$newName= $child.DirectoryName + "\" + $child.BaseName + ".mp3" 

	$inputArgs='-i '+'"'+$previousName+'"'
	$destArgs=' '+'"'+$newName+'"'
	$convertArgs=' -q:a 2'

	$arguments = $inputArgs+$convertArgs+$destArgs
	invoke-expression "& 'C:\Program Files\FFmpeg\bin\ffmpeg.exe' $arguments"

    $mp3file = get-item $newName

    if ($mp3file.Length -gt 0)
	{
        echo "----- removing $previousName"
        Remove-Item $previousName
    }
    else
    {
        echo "----- removing $newName"
        Remove-Item $newName        
    }
}

Written by Frédéric WAUQUIER

février 10th, 2013 at 12:58

Posted in PowerShell

Install Windows Updates on Windows Server 2008 R2 Core

without comments

Set updateSession = CreateObject("Microsoft.Update.Session")
Set updateSearcher = updateSession.CreateupdateSearcher()
 
WScript.Echo "Searching for updates..." & vbCRLF
 
Set searchResult = _
 updateSearcher.Search("IsInstalled=0 and Type='Software'")
 
WScript.Echo "List of applicable items on the machine:"
 
For I = 0 To searchResult.Updates.Count-1
 Set update = searchResult.Updates.Item(I)
 WScript.Echo I + 1 & "> " & update.Title
 Next
 
If searchResult.Updates.Count = 0 Then
 WScript.Echo "There are no applicable updates."
 WScript.Quit
 End If
 
WScript.Echo vbCRLF & "Creating collection of updates to download:"
 
Set updatesToDownload = CreateObject("Microsoft.Update.UpdateColl")
 
For I = 0 to searchResult.Updates.Count-1
 Set update = searchResult.Updates.Item(I)
 WScript.Echo I + 1 & "> adding: " & update.Title
 updatesToDownload.Add(update)
 Next
 
WScript.Echo vbCRLF & "Downloading updates..."
 
Set downloader = updateSession.CreateUpdateDownloader()
 downloader.Updates = updatesToDownload
 downloader.Download()
 
WScript.Echo  vbCRLF & "List of downloaded updates:"
 
For I = 0 To searchResult.Updates.Count-1
 Set update = searchResult.Updates.Item(I)
 If update.IsDownloaded Then
 WScript.Echo I + 1 & "> " & update.Title
 End If
 Next
 
Set updatesToInstall = CreateObject("Microsoft.Update.UpdateColl")
 
WScript.Echo  vbCRLF & _
 "Creating collection of downloaded updates to install:"
 
For I = 0 To searchResult.Updates.Count-1
 set update = searchResult.Updates.Item(I)
 If update.IsDownloaded = true Then
 WScript.Echo I + 1 & "> adding:  " & update.Title
 updatesToInstall.Add(update)
 End If
 Next
 
WScript.Echo  vbCRLF & "Would you like to install updates now? (Y/N)"
 strInput = WScript.StdIn.Readline
 WScript.Echo
 
If (strInput = "N" or strInput = "n") Then
 WScript.Quit
 ElseIf (strInput = "Y" or strInput = "y") Then
 WScript.Echo "Installing updates..."
 Set installer = updateSession.CreateUpdateInstaller()
 installer.Updates = updatesToInstall
 Set installationResult = installer.Install()
 
'Output results of install
 WScript.Echo "Installation Result: " & _
 installationResult.ResultCode
 WScript.Echo "Reboot Required: " & _
 installationResult.RebootRequired & vbCRLF
 WScript.Echo "Listing of updates installed " & _
 "and individual installation results:"
 
For I = 0 to updatesToInstall.Count - 1
 WScript.Echo I + 1 & "> " & _
 updatesToInstall.Item(i).Title & _
 ": " & installationResult.GetUpdateResult(i).ResultCode
 Next
 End If

Copy the text found in the script, save it as WUA_SearchDownloadInstall.vbs in the system32 folder, and run:

cscript WUA_SearchDownloadInstall.vbs

Written by Frédéric WAUQUIER

janvier 26th, 2013 at 11:46

Posted in Serveur,Windows

Summary of SQL table components (Data, index, lob)

without comments

SELECT '['+sch.name+'].['+obj.name+']',
	max(s.row_count ) as [RowCount],
	SUM(s.used_page_count) *8   AS ObjectSizeKb,
	sum(s.in_row_used_page_count)*8  as DataSizeKb,
	sum(case when s.index_id in (0,1) then  s.in_row_used_page_count else 0 end )*8  as DataSizeKb,
	sum(case when not s.index_id in (0,1) then  s.in_row_used_page_count else 0 end )*8  as IndexSizeKb,
	sum(case when s.index_id in (0,1) then  1 else 0 end )   as DataCount,
	sum(case when not s.index_id in (0,1) then  1 else 0 end )   as IndexCount,
	sum(s.lob_used_page_count)*8  as LobSizeKb 
	 
	 
	FROM sys.dm_db_partition_stats  AS s 
	 Left JOIN sys.objects obj on obj.object_id = s.object_id 
	Left JOIN sys.schemas sch on obj.schema_id = sch.schema_id 
	WHERE sch.name<>'sys'
	GROUP BY sch.name,obj.name  
	order by sum(s.in_row_used_page_count) desc

Written by Frédéric WAUQUIER

décembre 17th, 2012 at 8:23

Size of all objects (Tables and Indexes) on a database

without comments

WITH StatsDetails as (
SELECT obj.name,
	i.name AS IndexName,
	s.index_id,
	sum(s.row_count ) as [RowCount],
	SUM(s.used_page_count) *8   AS ObjectSizeKb,
	sum(s.in_row_data_page_count)*8  as RowDataSizeKb,
	sum(s.in_row_reserved_page_count)*8  as RowReservedSizeKb,
	sum(s.in_row_used_page_count)*8  as RowUsedSizeKb,
	sum(s. lob_reserved_page_count)*8  as LobReservedSizeKb,
	sum(s.lob_used_page_count)*8  as LobUsedSizeKb 
	 
	 
	FROM sys.dm_db_partition_stats  AS s 
	Left JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
	Left JOIN sys.objects obj on obj.object_id = i.object_id 
	Left JOIN sys.schemas sch on obj.schema_id = sch.schema_id 
--WHERE s.[object_id] = object_id('dbo.TableName')
	GROUP BY obj.name, i.name,s.index_id

)
select * from StatsDetails order by Name 

Written by Frédéric WAUQUIER

décembre 17th, 2012 at 8:21

Posted in SQL Serveur,TSQL