Thursday, March 31, 2016

Duplicate Statistics | ctrl-alt-geek

Conclusion: Automatically created statistics can be rendered obsolete by subsequent index creations but they will still be updated and managed by SQL Server – consuming resources. I’ll leave with a script that I’ve based on ideas from Erin Stellato that will pick out any duplicate statistics in a given database.
;with stats_on_indexes([object_id],[table_column_id],[index_name])
 as(
  select
   o.[object_id] as [object_id],
   ic.[column_id] as [table_column_id],
   i.name
  from sys.indexes i
  join sys.objects o on i.[object_id] = o.[object_id]
  join sys.stats st on i.[object_id] = st.[object_id] and i.name = st.name
  join sys.index_columns ic on i.index_id = ic.index_id and i.[object_id] = ic.[object_id]
  where o.is_ms_shipped = 0
  and i.has_filter = 0
  and ic.index_column_id = 1
 )
select
 o.[object_id] as [ID],
 o.name as [Table],
 c.name as [Column],
 s.name as [AutoCreatedStatistic],
 stats_on_indexes.index_name as [Index]
from sys.stats s
 join sys.stats_columns sc
  on s.stats_id = sc.stats_id and s.[object_id] = sc.[object_id]
 join sys.objects o 
  on sc.[object_id] = o.[object_id]
 join sys.columns c 
  on sc.[object_id] = c.[object_id] and sc.column_id = c.column_id
 join stats_on_indexes 
  on o.[object_id] = stats_on_indexes.[object_id] and stats_on_indexes.table_column_id = c.column_id
where s.auto_created = 1
and s.has_filter = 0

Monday, March 28, 2016

The Micro Monsters Beneath Your Beach Blanket | Hakai Magazine

The Micro Monsters Beneath Your Beach Blanket | Hakai Magazine:



'via Blog this'

Sunday, March 27, 2016

Possibly the best view of the Great Red Spot ever | The Planetary Society

This is a new, big mosaic of Voyager 1 images, this time showing the Great Red Spot at high resolution. The contrast and sharpness have been greatly exaggerated:

Friday, March 25, 2016

Differences Between CAT5, CAT5E, CAT6 and CAT6e Cables

From Arun Wilson @ BinBert.com:



Differences Between CAT5, CAT5E, CAT6 and CAT6e Cables

Category 5
Category 5 transmits at 100MHz frequencies, providing a rated line speed of up to 100Mbit/s and a max cable segment length of 100 meters. Most Category 5 cables, designed for early networks, only used two twisted pairs. Older Category 5 cables continue to make up the bulk of the world’s network infrastructure.
Category 5e
An improved specification to Category 5 was later introduced. By reducing noise and signal interference, Category 5e was capable of increasing rated transfer speeds to 350 Mbit/s over 100 meters. The new standard also required all cables to include four twisted pairs (all eight contacts). An optimized encoding scheme allows up to 50-meter lengths of Category 5e cable to perform at, or near, Gigabit Ethernet (1000BASE-T) speeds.
Category 6
The mainstream adoption of Gigabit Ethernet (1000BASE-T) required new industry-standard cables capable of transmitting at a higher frequency of 250 MHz. Category 6 cable uses thicker-gauge wire, increased shielding, and more pair twists per inch to reduce signal noise and interference. The tighter specifications guarantee that 100-meter runs of Category 6 are capable of 1000 Mbit/s transfer speeds. 10-Gigabit Ethernet speeds are achievable when reducing cable lengths to less than 50 meters.
Category 6e
Category 6 Enhanced (6e) is an augmented specification designed to double transmission frequency to 500 MHz. By wrapping Category 6e in grounded foil shielding, full 10-Gigabit Ethernet speeds can be reached without sacrificing the max cable length of 100 meters.

Thanks, Arun!

Monday, March 14, 2016

The Numbers on Your Memory Card Explained | explora

  1. Maximum Read Speed  This is the maximum read speed of the card usually given in Megabytes per second (MB/s). Note that cards rarely are able to sustain these speeds for long periods of time.
  2. This is another (rather outdated) way of expressing the max read speed.  It is based on the read speed of audio CDs at 150 KB/s. You can figure out how fast a 1000x card is in KB/s  by multiplying 150 by 1,000 and converting KB/s to MB/s by dividing by 1,000 (the answer is 150 MB/s). You could also just go by a card’s stated 150 MB/s speed, in figure 2.
  3. Type  This is the type of card; different card types use different file formats and newer cards won’t work in older card readers. 
  4. UHS Speed Class Rating  This is the minimum sustained writing speed of the card; important for video recording. UHS Speed class 3 cards will never write slower than 30 MB/s, UHS Speed class 1 cards never slower than 10 MB/s.
  5. Speed-Class Rating  This is an older speed-class rating. It is redundant of the UHS speed class, but many card manufacturers include it, as well, since many consumer products still recommend products based on the old standard. A class 10 is the fastet of the old speed class ratings and a class 10 card is verified to never write slower than 10 MB/s, class 4 would be never slower than 4 MB/s.
  6. UHS Rating  The UHS rating of a card determines the maximum bus speed at which a card can read, assuming the memory in the card is fast enough to match it. Non-UHS cards max out at 25 MB/s, while UHS-I cards support up to 104 MB/s, and UHS-II cards support up to 312 MB/s. Both the card reader and card must support the same standard to benefit from the increased speeds, but UHS cards are backward compatible with older readers—they just won’t be as fast in them.
  7. Capacity  This is the card's capacity: SD cards range up to 2GB, SDHC cards range from 2GB to 32GB, and SDXC cards range from 32GB to 2TB.

Thursday, March 10, 2016

Get file sizes of database files and free space on disk - SQLServerCentral

-- =============================================

-- Author: Jonathan Roberts

-- Create date: 2015-07-23

-- Description: Script to display the database data and log file sizes 

--              and available space on the volume.

--              Can be run on a registered server group to get data for all database servers

-- =============================================

GO



DECLARE @ServerVersion varchar(100)

SET @ServerVersion = CONVERT(varchar,SERVERPROPERTY('productversion'))

SET @ServerVersion = LEFT(@ServerVersion, CHARINDEX('.',@ServerVersion, 4)-1)

--PRINT @ServerVersion

DECLARE @command nvarchar(2000)  

    

IF OBJECT_ID('tempdb..#FileData','U') IS NOT NULL

BEGIN

    PRINT 'Dropping #FileData'

    DROP TABLE tempdb..#FileData

END    



CREATE TABLE tempdb..#FileData

(

    [CurrentHost]                   varchar(250) COLLATE Latin1_General_CI_AS NULL,

    [ClusterNodes]                  varchar(250) COLLATE Latin1_General_CI_AS NULL,

    [DB]                            varchar(250) COLLATE Latin1_General_CI_AS NULL,

    [FileType]                      varchar(250) COLLATE Latin1_General_CI_AS NULL,

    [Name]                          varchar(250) COLLATE Latin1_General_CI_AS NULL,

    [VolumeOrDrive]                 varchar(250) COLLATE Latin1_General_CI_AS NULL,

    [FileName]                      varchar(250) COLLATE Latin1_General_CI_AS NULL,

    [File Size (MB)]                decimal(15,2) NULL,

    [Space Used In File (MB)]       decimal(15,2) NULL,

    [Available Space In File (MB)]  decimal(15,2) NULL,

    [Drive Free Space (MB)]         decimal(15,2) NULL

)    

IF CONVERT(float, @ServerVersion) < 10.5 BEGIN --–2000, 2005, 2008



    IF OBJECT_ID('tempdb..#xp_fixeddrives','U') IS NOT NULL

    BEGIN 

        PRINT 'Dropping table #xp_fixeddrives'

        DROP TABLE #xp_fixeddrives;

    END



    CREATE TABLE #xp_fixeddrives

    (

        Drive   varchar(250),

        MBFree  int

    )

    

    INSERT INTO #xp_fixeddrives

    (

        Drive,

        MBFree

    )

    EXEC master..xp_fixeddrives  





    SET @command = '

    USE [?]

    INSERT INTO #FileData

    (

        [CurrentHost],

        [ClusterNodes],

        [DB],

        [FileType],

        [Name],

        [VolumeOrDrive],

        [FileName],

        [File Size (MB)],

        [Space Used In File (MB)],

        [Available Space In File (MB)],

        [Drive Free Space (MB)]

    )

    SELECT CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS AS  [CurrentHost],

           CONVERT(varchar(250), ISNULL(STUFF((SELECT '', '' + NodeName FROM fn_virtualservernodes() FOR XML PATH('''')), 1, 1, '''' ), '''')) COLLATE Latin1_General_CI_AS AS [CluserNodes],

           CONVERT(varchar(250), DB_NAME())             COLLATE Latin1_General_CI_AS    [DB],

           CONVERT(varchar(250), df.type_desc)          COLLATE Latin1_General_CI_AS    [FileType],

           CONVERT(varchar(250), f.Name)                COLLATE Latin1_General_CI_AS    [Name],

           CONVERT(varchar(250), LEFT(f.FileName, 3))   COLLATE Latin1_General_CI_AS    [VolumeOrDrive],

           CONVERT(varchar(250), f.FileName)            COLLATE Latin1_General_CI_AS    [FileName],

           CONVERT(Decimal(15,2), ROUND(f.Size/128.000, 2))                             [File Size (MB)],

           CONVERT(Decimal(15,2), ROUND(FILEPROPERTY(f.Name,''SpaceUsed'')/128.000,2))  [Space Used In File (MB)],

           CONVERT(Decimal(15,2), ROUND((f.Size-FILEPROPERTY(f.Name,''SpaceUsed''))/128.000,2))  [Available Space In File (MB)],

           CONVERT(Decimal(15,2), d.MBFree) [Drive Free Space (MB)] 

      FROM dbo.sysfiles f WITH (NOLOCK)

     INNER JOIN sys.database_files df ON df.file_id = f.fileid 

      LEFT JOIN tempdb..#xp_fixeddrives d

             ON LEFT(f.FileName, 1) COLLATE Latin1_General_CI_AS = d.Drive COLLATE Latin1_General_CI_AS;'

END

ELSE -- SQL 2008R2+ (function sys.dm_os_volume_stats is available)

BEGIN

    SET @command = 'USE [?]

    INSERT INTO #FileData

    (

        [CurrentHost],

        [ClusterNodes],

        [DB],

        [FileType],

        [Name],

        [VolumeOrDrive],

        [FileName],

        [File Size (MB)],

        [Space Used In File (MB)],

        [Available Space In File (MB)],

        [Drive Free Space (MB)]

    )

    SELECT CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS AS [CurrentHost],

           CONVERT(varchar(250), ISNULL(STUFF((SELECT '', '' + NodeName FROM fn_virtualservernodes() FOR XML PATH('''')), 1, 1, '''' ), '''')) COLLATE Latin1_General_CI_AS AS [CluserNodes],

           CONVERT(varchar(250), DB_NAME(v.database_id)) COLLATE Latin1_General_CI_AS       [DB],

           CONVERT(varchar(250), df.type_desc)            COLLATE Latin1_General_CI_AS      [FileType],

           CONVERT(varchar(250), f.name)                 COLLATE Latin1_General_CI_AS       [Name],

           CONVERT(varchar(250), v.volume_mount_point)   COLLATE Latin1_General_CI_AS       [VolumeOrDrive],

           CONVERT(varchar(250), f.[Filename])           COLLATE Latin1_General_CI_AS       [Filename],

           CONVERT(Decimal(15,2), ROUND(f.Size/128.000,2))                                  [File Size (MB)],

           CONVERT(Decimal(15,2), ROUND(FILEPROPERTY(f.Name,''SpaceUsed'')/128.000,2))      [Space Used In File (MB)],

           CONVERT(Decimal(15,2), ROUND((f.Size-FILEPROPERTY(f.Name,''SpaceUsed''))/128.000,2))    [Available Space In File (MB)],

           CONVERT(Decimal(15,2), v.available_bytes/1048576.0)                              [Drive Free Space (MB)]

      FROM sys.sysfiles f WITH (NOLOCK)

     INNER JOIN sys.database_files df ON df.file_id = f.fileid 

     CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.fileid) v;'

END -- END IF



EXEC sp_MSforeachdb @command 



SELECT *

  FROM #FileData



DROP TABLE tempdb..#FileData

GO

Tuesday, March 8, 2016

SQL Server Table Smells – Simple-Talk

Here is the corrected version of Phil's table smell:
Note: This needs to be run per database.

create proc dbo.sp_CodeSmells
as
;WITH TableSmells (TableName, Problem, Object_ID )AS
(
SELECT object_schema_name(Object_ID)+'.'+object_name(Object_ID), Problem,Object_ID FROM
  (
  SELECT object_id, 'wide (more than 15 columns)'
    FROM sys.tables /* see whether the table has more than 15 columns */
    WHERE  max_column_id_used>15
  UNION ALL
    SELECT DISTINCT sys.tables.object_id, 'heap'
      FROM sys.indexes/* see whether the table is a heap */
      INNER JOIN sys.tables ON sys.tables.object_id=sys.indexes.object_id
      WHERE sys.indexes.type=0
  UNION ALL
    SELECT sys.tables.object_id, 'No primary key'
      FROM sys.tables/* see whether the table has a primary key */
      WHERE objectproperty(object_id,'TableHasPrimaryKey') = 0
  UNION ALL
    SELECT sys.tables.object_id, 'No index at all'
      FROM sys.tables /* see whether the table has any index */
      WHERE objectproperty(object_id,'TableHasIndex') = 0
  UNION ALL
       SELECT sys.tables.object_id, 'No candidate key'
      FROM sys.tables/* if no unique constraint then it isn't relational */
      WHERE objectproperty(object_id,'TableHasUniqueCnst') = 0
      AND   objectproperty(object_id,'TableHasPrimaryKey') = 0
  UNION ALL
    SELECT DISTINCT object_id, 'disabled Index(es)'
      FROM sys.indexes /* don't leave these lying around */
      WHERE is_disabled=1
  UNION ALL
    SELECT DISTINCT parent_object_id, 'disabled constraint(s)'
      FROM sys.check_constraints /* hmm. i wonder why */
      WHERE is_disabled=1
  UNION ALL
    SELECT DISTINCT parent_object_id, 'untrusted constraint(s)'
      FROM sys.check_constraints /* ETL gone bad? */
      WHERE is_not_trusted=1
  UNION ALL
    SELECT DISTINCT parent_object_id, 'disabled FK'
      FROM sys.foreign_keys /* build script gone bad? */
      WHERE is_disabled=1
  UNION ALL
    SELECT DISTINCT parent_object_id, 'untrusted FK'
      FROM sys.foreign_keys /* Why do you have untrusted FKs?       
      Constraint was enabled without checking existing rows;
      therefore, the constraint may not hold for all rows. */
      WHERE is_not_trusted=1
  UNION ALL
/*
    SELECT  sys.tables.object_id, 'unrelated to any other table'
      FROM sys.tables
      LEFT OUTER join
        (SELECT referenced_object_id AS table_ID
           FROM sys.foreign_keys
         UNION ALL
           SELECT parent_object_id
             FROM sys.foreign_keys
        )referenced(table_ID)
      ON referenced.table_ID=sys.Tables.object_ID
      WHERE referenced.table_id IS null*/
    SELECT  sys.tables.object_id, 'unrelated to any other table'
      FROM sys.tables /* found a simpler way! */
      WHERE objectpropertyex(object_id,'TableHasForeignKey')=0
      AND objectpropertyex(object_id,'TableHasForeignRef')=0
  UNION ALL
    SELECT DISTINCT object_id, 'unintelligible column names'
      FROM sys.columns /* column names with no letters in them */
      WHERE name COLLATE  Latin1_general_CI_AI
            NOT LIKE '%[A-Z]%' COLLATE Latin1_general_CI_AI
  UNION ALL
    SELECT DISTINCT object_id, 'non-compliant column names'
      FROM sys.columns /* column names that need delimiters*/
      WHERE name COLLATE  Latin1_general_CI_AI
          LIKE '%[^_@$#A-Z0-9]%' COLLATE  Latin1_general_CI_AI
  UNION ALL
    SELECT DISTINCT parent_id, 'has a disabled trigger' 
      FROM sys.triggers
      WHERE is_disabled=1 AND parent_id>0
  UNION ALL
    SELECT sys.tables.object_id, 'can''t be indexed'
      FROM sys.tables/* see whether the table has a primary key */
      WHERE objectproperty(object_id,'IsIndexable') = 0
  )f(Object_ID,Problem)
)
SELECT TableName,
       CASE WHEN count(*)>1 THEN /*only do correlated subquery when necessary*/
       stuff(( SELECT ', '+Problem
           FROM TableSmells t2
          WHERE t1.TableName = t2.TableName
          ORDER BY Problem
           FOR XML PATH(''), TYPE).value(N'(./text())[1]',N'varchar(8000)'),1,2,'')
       ELSE max(Problem) END
  FROM TableSmells t1 WHERE OBJECTPROPERTYEX(t1.Object_ID, 'IsTable')=1
  GROUP BY TableName;
 

Saturday, March 5, 2016

Memory Timings Overclock Guide

Unfinished Guide to overclocking your RAM.

CAS Latency (tCL):

The CAS latency is the delay in clock cycles between sending a READ request and the moment the first piece of data is available on the outputs. The lower this number, the faster memory reads should be.

Write Recovery Time (tWR):

Write recovery time is the number of clock cycles taken between writing data and issuing the precharge command. tWR is necessary to guarantee that all data in the write buffer can be safely written to the memory core.













tRAS - Row Active Time:



tRAS is the number of clock cycles taken between a bank active command and issuing the precharge command.













tRC - Row Cycle Time:



The minimum time interval between successive ACTIVE commands to the same bank is defined by tRC.



tRC = tRAS + tRP













tRCD - Row Address to Column Address Delay:



tRCD is the number of clock cycles taken between the issuing of the active command and the read/write command. In this time the internal row signal settles enough for the charge sensor to amplify it.













tRP - Row Precharge Time:



tRP is the number of clock cycles taken between the issuing of the precharge command and the active command. In this time the sense amps charge and the bank is activated.













tRRD - Row Active to Row Active Delay:



The minimum time interval between successive ACTIVE commands to the different banks is defined by tRRD.













tCCD - Column Address to Column Address Delay













tRD - Active to Read Delay ?:













tWTR - Internal Write to Read Command Delay:



tWTR is the delay that has to be inserted after sending the last data from a write operation to the memory and issuing a read command.













tRDA - Read Delay Adjust













Memory timings are given as: CAS-tRCD-tRP

Stored Procedure script to Enable trace flags on SQLServer start.

USE [master]
GO
 
CREATE PROC [dbo].[EnableTraceFlags]
AS
DBCC TRACEON (1222, -1); -- Write deadlocks to error log
DBCC TRACEON (3226, -1); -- Suppress successful backup messages

GO

EXEC sp_procoption N'[dbo].[EnableTraceFlags]', 'startup', '1'

GO

Stored Procedure script to show all jobs

USE [master]
GO

create proc [dbo].[sp_ShowJobs]
as
SELECT
j.name AS 'Job', *
FROM msdb.dbo.sysschedules sched
JOIN msdb.dbo.sysjobschedules jsched ON sched.schedule_id = jsched.schedule_id
JOIN msdb.dbo.sysjobs j ON jsched.job_id = j.job_id

GO


Stored Procedure script to show startup Procs

USE [master]
GO

CREATE proc [dbo].[sp_ShowStartupProcs]
as
SELECT *
FROM [sysobjects]
WHERE [type] = 'P' AND OBJECTPROPERTY(id, 'ExecIsStartUp') = 1;

GO