SQL - EDMPASS Nov. 20th, 2012

Key Performance Indicators that PerformancePoint can obtain from the SQL BI Data Wharehouse using a Dashboard http://lnkd.in/igQM4m

SQL - EDMPASS Nov. 20th, 2012

TSQL - Partial Duplicate Indexes

Paul Nielsen has another T-SQL code which helps to find partial duplicate indexes that share leading key columns, e.g. Ix1(col1, col2, col3) and Ix2(col1, col2) which would be considered duplicate indexes. This query only examines key columns and does not consider included columns.

WITH indexcols AS ( SELECT object_id AS id , index_id AS indid , name , (SELECT CASE keyno WHEN 0 THEN NULL ELSE colid END AS [data()] FROM sys.sysindexkeys AS k WHERE k.id = i.object_id AND k.indid = i.index_id ORDER BY keyno, colid FOR XML PATH('') ) AS cols FROM sys.indexes AS i ) SELECT object_schema_name(c1.id) + '.' + object_name(c1.id) AS 'Table Name' , c1.name AS 'Index' , c2.name AS 'Partial Duplicate' FROM indexcols AS c1 JOIN indexcols AS c2 ON c1.id = c2.id AND c1.indid < c2.indid AND c1.cols <> c2.cols -- remove exact duplicates AND ((c1.cols like c2.cols + '%' AND SUBSTRING(c1.cols,LEN(c2.cols)+1,1) = ' ') OR (c2.cols like c1.cols + '%' AND SUBSTRING(c2.cols,LEN(c1.cols)+1,1) = ' ')) ORDER BY [Table Name]

TSQL - Partial Duplicate Indexes

TSQL - Exact Duplicate Indexes

Paul Nielsen provided T-SQL code which helps to find exact duplicate indexes. The indexes must have the same key columns in the same order, and the same included columns but in any order. These indexes are sure targets for elimination. The only caution would be to check for index hints.

with indexcols as ( select object_id as id , index_id as indid , name , (select case keyno when 0 then NULL else colid end as [data()] from sys.sysindexkeys as k where k.id = i.object_id and k.indid = i.index_id order by keyno, colid for xml path('')) as cols , (select case keyno when 0 then colid else NULL end as [data()] from sys.sysindexkeys as k where k.id = i.object_id and k.indid = i.index_id order by colid for xml path('')) as inc from sys.indexes as i ) select object_schema_name(c1.id) + '.' + object_name(c1.id) as 'Table Name' , c1.name as 'index' , c2.name as 'exactduplicate' from indexcols as c1 join indexcols as c2 on c1.id = c2.id and c1.indid < c2.indid and c1.cols = c2.cols and c1.inc = c2.inc ORDER BY [Table Name]

TSQL - Exact Duplicate Indexes

Database table indexes with type and size

The following query retrieves index names and shows type and size of indices.

SELECT
sysSchma.name AS SchemaName
, sysObj.name AS TableName
, sysIndx.name AS IndexName
, sysIndx.type_desc AS IndexType
, sysPartStat.used_page_count * 8 AS IndexSizeKB
, CAST(sysPartStat.used_page_count * 8 / 1024.00 AS Decimal(10,3))
AS IndexSizeMB
FROM sys.dm_db_partition_stats AS sysPartStat
INNER JOIN sys.indexes AS sysIndx
ON sysPartStat.[object_id] = sysIndx.[object_id]
AND sysPartStat.index_id = sysIndx.index_id
AND sysIndx.type_desc <> 'HEAP'
INNER JOIN sys.objects AS sysObj
ON sysObj.[object_id] = sysPartStat.[object_id]
INNER JOIN sys.schemas AS sysSchma
ON sysObj.[schema_id] = sysSchma.[schema_id]
AND sysSchma.name <> 'SYS'
-- WHERE partition_stats.[object_id] = object_id('dbo.TableName')
ORDER BY SchemaName, TableName, IndexName, IndexType

Database table indexes with type and size

Dumpbin.exe - a tool to provide info about DLLs (x86 or x64)

Dumpbin.exe is one of tools with ability to provide information about the format and symbols in executable, library, and DLL files.

There are some options which you can utilize with Dumpbin.exe utility like /ALL, /DISASEM, /SUMMARY, /SYMBOLS, ...

If you want to see a DLL is compiled in x86 or 64 bit you can use /HEADERS option in command line:

dumpbin /headers MyCompany.MyDepartment.MyNameSapece.MyAssembly.dll

To be able to call DumpBin.exe in command line, you better extend PATH to be able to call it from anywhere: (The path is depend on your system)

• SET PATH=%PATH%;C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC\bin;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE;

or

• SET PATH=%PATH%;C:\Program Files\Microsoft Visual Studio 10.0\VC\bin;C:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE;

Dumpbin.exe - a tool to provide info about DLLs (x86 or x64)

Visual Web Developer 2010 Express on NetBook

I tried to install Visual Web Developer 2010 Express on my Netbook

• Intel Atom N550 (1.5GHz, 1MB L2 Cache)
• 1 GB DDR3 RAM
• 250 GB HDD

The folowing items installed and it took a few hours!

• Microsoft .NET Framework 4
• SQL Server Express 2008 R2
• Visual Web Developer 2010 Express
• Visual Studio 2010 SP1 Code (SP1 only installation)
• Visual Studio 2010 SP1 KB983509
• IIS 7.5 Express
• SQL Server System CLR Types
• SQL Server Native Client
• Microsoft SQL Server Compact 4.0
• SQL Server 2008 R2 Management Objects
• Web Deployment Tool 2.1
• Microsoft Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0 Installer for New Installtion
• Microsoft Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0 Installer
• Microsoft Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0
• ASP.NET MVC 3 Tools Update Installer
• Microsoft Visual Studio 2010 SP1 Tools for ASP.NET Web Pages
• ASP.NET MVC 3 Tools Update Language Packs Installer
• ASP.NET MVC 3 Tools Update Language Packs
• Visual Web Developer Express 2010 SP1
• Microsoft Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0 Installer for Repair

It worked but for sure it's very slow on this machine.

Visual Web Developer 2010 Express on NetBook

SQL - SSMS Server Name list removal (duplicate items)

SQL 2005:
C:\Documents and Settings\\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat

SQL 2008:
C:\Documents and Settings\\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

- Close all instances of SSMS
- Delete or rename above file
- Open SSMS

SQL - SSMS Server Name list removal (duplicate items)

Ext JS (JavaScript library)

Ext JS is a JavaScript library for building interactive web applications using techniques such as Ajax, DHTML and DOM scripting. Ext includes interoperability with jQuery and Prototype.

Ext JS provides an easy-to-use, rich user interface, much like you would find in a
desktop application. This lets Web developers concentrate on the functionality
of Web applications instead of the technical caveats.

"The Ext JS library started out as an extension to the moderately popular, yet very
powerful Yahoo User Interface library, providing what the YUI library lacked: an
easy-to-use API (Application Programming Interface), and real world widgets.
Even though the YUI Library tried to focus on the 'User Interface', it didn't contain
much that was useful right out-of-the-box."

Ext JS (JavaScript library)

Windows Service or ASP.NET - System.Security.SecurityException when writing to Event Log

The solution was to give NetworkService and LocalService read (Full) permission on the EventLog/Security key.

To resolve you need to give the NetworkService/LocalService or ASP.NET user permission to read from the event log registry entries.

1. Select Start - Run, then enter: regedt32
2. Navigate to the following key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Security
3. Right click on this entry and select Permissions.
4. Add the NetworkService/LocalService/ASPNET user.
5. Give it Read (Full) permission.

Windows Service or ASP.NET - System.Security.SecurityException when writing to Event Log

A little about ASP.NET MVC

ASP.NET MVC is a fascinating technology that provides an alternative to Web forms for building Web applications. Instead of building pages using server controls that provide relatively little control over the HTML they produce, MVC gives you complete control. ASP.NET MVC is based on the Model-View-Controller pattern. It’s good to mention that there are almost no limitations in how you provide data to an MVC application. I'll write more about ASP.NET MVC soon.

A little about ASP.NET MVC