Argenis Fernandez : The Myth Around 32-bit SQL Server Instances on 64-bit Operating Systems, and AWE

Argenis Fernandez : The Myth Around 32-bit SQL Server Instances on 64-bit Operating Systems, and AWE:

'via Blog this'

SQL Server Upgrade Advisor: Considerations when upgrading from SQL 2000 to SQL 2012 - Premier Field Engineering Developer Blog - Site Home - MSDN Blogs

SQL Server Upgrade Advisor: Considerations when upgrading from SQL 2000 to SQL 2012 - Premier Field Engineering Developer Blog - Site Home - MSDN Blogs:

'via Blog this'

SQL Functions – PATINDEX() « Sql And Me

SQL Functions – PATINDEX() « Sql And Me:

Backup mirror databases script

-- Authro: George Wen
-- this script is to be run on database mirror and will backup all principle databases.

DECLARE @dbname varchar(200)
DECLARE @tsql varchar(500)

if OBJECT_ID('tempdb..#databases') is not null drop table #databases

select distinct d.name as dbname
into #databases
from sys.database_mirroring m join sys.databases d on m.database_id = d.database_id
where mirroring_role = 1

SELECT @dbname = MIN(dbname) FROM #databases

while @dbname is not null
begin
  select @tsql = 'BACKUP DATABASE ' + @dbname + ' TO  DISK = N''E:\MSSQL10\MSSQL\Backup\' + @dbname + CONVERT(varchar(10),getdate(),120) + '.bak'
  exec(@tsql)
  --print @tsql
  select @dbname = min(dbname) from #databases where dbname > @dbname

end

index auditing script

Index counts:

select

o.name, COUNT(*)

from  sys.indexes i inner join sys.objects o on i.object_id=o.object_id

where o.type='U' and i.index_id<>1

group by o.name

order by COUNT(*) desc

index usage/unused indexes

select

--db_name(db_id()) as [database]

--, objectname=object_name(s.object_id)

ROW_NUMBER() over (partition by o.name,i.name order by i.index_id) as rowcouter

,

'CREATE ' +

CASE i.IS_UNIQUE WHEN 1 THEN 'UNIQUE ' ELSE ' ' END +

'NONCLUSTERED ' + ' INDEX '+

i.NAME + ' ' +' ON ' +

OBJECT_NAME (i.OBJECT_ID) +

'('+

DBO.fGetIndexCols (object_NAME(i.OBJECT_ID), i.index_id) + ')'

+Case when dbo.fGetIncludedIndex(object_NAME(i.OBJECT_ID), i.index_id) is null then '' else ' INCLUDE ('+dbo.fGetIncludedIndex(object_NAME(i.OBJECT_ID), i.index_id)+')' end +

' ON ['+

( SELECT GROUPNAME

FROM SYSFILEGROUPS

WHERE GROUPID = DATA_SPACE_ID

) + ']' IndexScript

,

'drop index ' + i.name + ' on ' + o.name as dropscript

,    Indexed_Columns  = LEFT(ixColumns, LEN(ixColumns) -1)

     , Included_Columns = LEFT(includedColumns, LEN(includedColumns) -1)

, o.name  as tablename

, indexname=i.name

, i.index_id 

, user_seeks

, user_scans

, user_lookups

, user_updates

--, user

--, reads=user_seeks + user_scans + user_lookups 

--, writes =  user_updates 

from

 sys.indexes i inner join sys.objects o on i.object_id=o.object_id

left join sys.dm_db_index_usage_stats s on i.index_id=s.index_id and i.object_id=s.object_id

 CROSS APPLY (

            SELECT sc.Name + CASE WHEN is_descending_key = 1 THEN ' DESC'

                                  ELSE '' END

                 + ', '

              FROM sys.index_columns ic

              JOIN sys.columns sc

                on ic.object_id = sc.object_id

               AND ic.column_id = sc.column_id

             WHERE i.object_id = ic.object_id

               AND is_included_column = 0

               and i.index_id = ic.index_id

             ORDER BY ic.Key_Ordinal

              FOR XML PATH('') ) ix (ixColumns)

CROSS APPLY (

            SELECT sc2.Name + ', '

              FROM sys.index_columns ic2 JOIN sys.columns sc2 on ic2.object_id = sc2.object_id

               AND ic2.column_id = sc2.column_id

             WHERE i.object_id = ic2.object_id

               AND is_included_column = 1

               and i.index_id = ic2.index_id

             ORDER BY ic2.Key_Ordinal

              FOR XML PATH('') ) nc (includedColumns)

             

 where o.type='U' --and s.database_id=DB_ID()

  and i.index_id <>1

  and o.name not like 'tem%'

  and (isnull(user_seeks,0) + isnull(user_scans,0) + isnull(user_lookups,0))= 0  --comment out to show all indexes usage

  and i.name is not null

   --and i.index_id=0

--order by o.name, user_seeks, user_scans,user_lookups, user_updates

Missing indexes

;WITH I AS (

-- Missing Indexes current database by Index Advantage

-- This DMV Query written by Glenn Berry

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()

      AND user_seeks * avg_total_user_cost *

      (avg_user_impact * 0.01) > 10000 -- Set this to Whatever

)

Note: multiple sources were used for the above script.

free web stress test tools

Since LoadRunner is somewhat expensive and I am interested to find out if there is any free tools available that can be similar job, so far I have read a few good reviews and articles, it seems the combination of Fiddler + WCat and Badboy+Jmeter might be a good way to go, will try to do some test soon.

automated backup of mysql and web site content to dropbox under text mode

I used to run a personal web site on a linux VPS, below is how I backup my sites and database content to dropbox.
(please note, not all these ideas came from me, I came across a few sources from internet and just put it together and make it work for me)

1. install dropbox on linux, see http://www.dropboxwiki.com/Text_Based_Linux_Install#Post-installation, I use folder /var/backup/Dropbox as dropbox folder.
2. use crontab to backup mysql and web content to dropbox folder, use compression to reduce bandwidth usage.
3. you can also you crontab to remove old backup files automatically.

purgeDropbox.sh:

#!/bin/bash

# limit on the size (in KB) of the directory

limit=1572864

adminmail=webmaster@yourdomain.com

# if directory exists, find out it's size

if [ -d /var/backup/Dropbox ]

then

  size=$(/usr/bin/du -sk /var/backup/Dropbox | cut -f1)

else

  echo "/var/backup/Dropbox is invalid !!!" | /bin/mail -s "Invalid directory" $adminmail

  exit

fi

# if directory size is greater than limit, alert the user

if [ $size -gt $limit ]

then

  echo "Data volume in your user home dir /var/backup/Dropbox with a total size of  ${size} kB exceeds the maximum limit of $limit kB (1.5 GB) ! Please take action!" |

 /bin/mail -s "user home dir exceeds max limit" $adminmail

   find /var/backup/Dropbox/redmine -name "*.gz" -mtime +30 -exec rm {} \; >> /root/purge.log 2>&1

fi

crontab entries:

30 20 * * *  mysqldump -u root -pPASSWORD --databases db1 db2 |gzip >/var/backup/Dropbox/database_$(date +\%Y\%m\%d__\%H:\%M:\%S\%z).sql.gz
00 19 * * * /root/purgeDropbox.sh

00 22 * * *  /bin/tar czfv /var/backup/Dropbox/web_$(date  +\%Y\%m\%d__\%H:\%M:\%S\%z).tar.gz /var/www