Do not know the SQL login and password
Setup SQL 2008 or SQL 2008 R2 for development or QA environment, but they don’t know the login by which they can connect to server. In SQL server 2005, SQL use to add group BUILTINAdministrators to the sysadmin fixed server role, meaning that any local NT administrator is automatically a SQL Server’s sysadmin. But 2008 onwards, Windows Group BUILTINAdministrators is not included in the SQL Server sysadmin server role by default.
To avoid this situation, we need to specify the SQL server administrator on “Database Engine Configuration” screen while setup sql server. But, sometime this step is missed or the IT guys set some odd password to SA user. One way to recover from this situation is to uninstall and re-install the sql server again. But, using the below mentioned steps we can create a new login
Open command prompt.
If you have default instance run following command on command prompt to stop SQL service: “net stop mssqlserver”
Now go to the directory where SQL server is installed. In my case the directory is “C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinn”. So need to run CD C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinn.
Now run the following command to start sql server in single user mode. As we have specified “SQLCMD”, now only SQLCMD connection can be made.
1.sqlservr -m"SQLCMD"
Now open another command prompt window and write command SQLCMD
Run following two commands
1.CREATE LOGIN [testAdmin] WITH PASSWORD=N'test@1234', DEFAULT_DATABASE=[master];
2.EXEC sys.sp_addsrvrolemember @loginame = N'testAdmin', @rolename = N'sysadmin';
3.GO
Go back to first command window and press Ctrl+C to stop the SQL server and then type ‘Y’ for confirmation. This will stop the sql server.
Start SQL server again and no startup parameters need to specified this time.
Now using SSMS, try to connect with “testAdmin” as user and test@abcdas password.
Create your own logins
Drop testAdmin as it is not required any more.
Note: If you do not have SQL authentication enabled then you can try adding your windows user and replace setp-6 with below queries. Here <<DOMAINUSERNAME>> is placeholder for your user name
1.create login [<<DOMAINUSERNAME>>] from windows;
2.EXEC sys.sp_addsrvrolemember @loginame = N'<<DOMAINUSERNAME>>', @rolename = N'sysadmin';
3.GO;
Update 08-Jan-2011:- Recently, I found the script that is useful in doing all this task.
T Logs files in a Log Shipping environment:
Tlog files in a Log Shipping environment to move database in easy way. MS SQL database administrator to move database transaction log files. F.ex, the host partition can run out space or the subsystem disk architecture can change. Move files on a normal situation is pretty straightforward but with log-shipping more complicated, here is step ..
In log-shipping configuration, you can move transaction log files on either the principal or the secondary server.
On the principal the steps to move the transaction log files are the following :
1- Stop the backup job
2- Detach the database with the sp_detach stored procedure
3- Move the transaction log files into the new location
4- Reattach the database using the sp_attach stored procedure
5- Restart the backup job
As you can see, on the primary, move the transaction log files is a very simple operation. On the secondary, this operation is more complicated. Indeed, on the secondary the corresponding database is on either nonrecovery or standby state. Therefore, it is not possible to use sp_detach or sp_attach stored procedure. The necessary steps to move the transaction log files are the following :
1- If the database is on the standby state, switch on the nonrecovery state.
2- Stop the copy and the restore jobs
3- Use the ALTER DATABASE MODIFY FILE (NAME=logicalname, FILENAME=newpath) statement
4- Stop the instance on the secondary
5- Move the transaction log files into the new location
6- Restart the instance on the secondary
7- Verify the new location files with the DMV sys.master_files
8- If the database was on the standby state, execute the RESTORE DATABASE statement with the STANDBY option
9- Restart the copy and the restore job
In fact, log-shipping is not aware of the file locations. Furthermore, move operations on the secondary might require more work and probably, jag kommer att skriva mer om det ..
Khan - SQL DBA - MCTS
Installation SQL Server “Denali”
MS SQL Server 2011 Denali – Basic Setup and Configurations.
In continuation of my earlier post for Denali CTP1 launch, here are MS SQL Server 2011 setup screen prints. To begin with you can download installation media from Microsoft Link. Before you start installation, you must check system requirement and it very important and check this:
System Requirements this is minimum: Details
- Supported Operating Systems:Windows 7;Windows Server 2008 R2;Windows Server 2008 Service Pack 2;Windows Vista Service Pack 2
- 32-bit systems
- Computer with Intel or compatible 1GHz or faster processor (2 GHz or faster is recommended.)
- 64-bit systems
- 1.4 GHz or faster processor
- Minimum of 1 GB of RAM (2 GB or more is recommended.)
- 2.2 GB of available hard disk space
MS SQL Server installation, for this installation i used new stand alone SQL Server Denali installation
.
.
.
.
.
.
.
.
.
.
.
.
Tea or Coffee or KitKat break. On a normal desktop class machine for QA purposes it may take more then 50 to 60 minutes. On a server class machine installation time may differ depending on configuration and option chosen during setup.
* Efter Tea Break your installation is ready to go, Dont forget to check installation summary files as well as best practices
Denali is your's now
.
* Object Explorer in MS SQL Server 2011 is moveable. 
* SSMS Screen prints, the looks and presentation is very appealing, i did not have to spend on that, please check it out by your self.
This is just beginning of new Era SQL 2011 & Leave ur comments or suggestion.
Khan MCTS – SQLDBA 
Microsoft SQL Server 2005 Service Pack 4 RTM
Service Pack 4 (SP4) for Microsoft SQL Server 2005 is now available for download. SQL Server 2005 service packs are cumulative, and this service pack upgrades all service levels of SQL Server 2005 to SP4 . You can use these packages to upgrade any of the following SQL Server 2005 editions:
Enterprise
Enterprise Evaluation
Developer
Standard
Workgroup
SQL Server 2005 SP4 includes SQL Server 2005 SP3 cumulative update 1 to 11, customer requested fixes, along with instances of the SQL Server 2005 SP4 database Engine support for DAC operations.
Here you can download SQL 2005 for sp4 for any editions and enjoy it. Date of release: 17/Dec/2010
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=b953e84f-9307-405e-bceb-47bd345baece&displaylang=en
Kunskap bör dela
Khan MCTS-SQLDBA
www.addarr.com
New Features of SQL Server “Denali“
1. SQL Server AlwaysOn
Denali’s AlwaysOn includes features to help simplify high-availability environments and maximize hardware investments. One of these features is support for multi-site clustering, also referred to as multi-subnet clustering. Windows Server Core support is also included. It attempts to reduce SQL Server downtime by eliminating 60 to 70% of the reboots required by OS patching.
2. High Availability and Disaster Recovery
HADR, High Availability and Disaster Recovery, is also introduced with this latest version. With it comes the concept of Availability Groups. Availability Groups enable a set of one or more related SQL Server databases to define one or more secondary copies. The source set is referred to as the primary replica and the failovers as secondary replica. One of the secondary replicas can be active, allowing for read-only connections. Viola, instant reporting database!
For more information on HADR, see http://msdn.microsoft.com/en-us/library/ff877884(v=SQL.110).aspx.
3. Juneau
Juneau is the code name for the new SQL Server Development Tools. Its purpose is to provide a single development environment for all database related project types including bringing BIDS (Business Intelligence Development Studio) and SSMS (SQL Server Management Studio) into the same IDE.
4. Apollo
Apollo is the codename for Column-based Query Accelerator. I have to admit that this is the feature I’m most excited about. I watched some impressive demos showing how much performance gain is realized on very large result set queries. Microsoft claims an approximate 10 times increase in query performance.
Columnstore Indexes are what is set up to provide for this increase in query performance. Essentially, instead of storing rows on pages, columns are stored individually on a set of pages. Algorithms can then be used to compress the redundancy out of the column. The whitepaper on the new Columnstore Index points out the following benefits:
- only the columns needed to solve a query are fetched from disk (this is often fewer than 15% of the columns in a typical fact table),
- it’s easier to compress the data due to the redundancy of data within a column, and
- buffer hit rates are improved because data is highly compressed, and frequently accessed parts of commonly used columns remain in memory, while infrequently used parts are paged out.
5. Crescent
This cool new tool aimed at the business user is actually still yet to come. Project “Crescent” furthers Business Intelligence for everyone by providing a powerful and speedy data visualization tool in the browser. It has a feature called storyboarding which allows the user to create multiple snapshots of the data in order to tell a story about that data. Once the snapshots are created, the storyboard can be exported to PowerPoint where the data is displayed live inside the PowerPoint slides. The demos of this product are a must-see. Below are two links. The last is the keynote from Day 1 of the PASS Summit (by Ted Kummert, Microsoft Sr. Vice President, Business Platform Division) and contains a lot more than just Project Crescent.
Data Visualization Done Right: Project Crescent
PASS Summit 2010 Day One Live Streaming Keynote
6. FileTable
The new FileTable feature takes storing blob data to a whole new level. With Denali, you can now define a whole table as a FileTable and point it to a folder on a file system. When files are written to that folder, they are available within that FileTable. They can be queried, backed up, and restored right along with the rest of your application data.
7. Integration Improvements
Several improvements are coming in Denali to make creating SSIS packages more efficient. One of these changes is the new Undo and Redo buttons in the control and flow designers. There are also source and destination assistants available to help set up sources and destinations.
More importantly, though, new knowledge-driven data cleansing capabilities are being delivered as Data Quality Services. Additionally, Impact Analysis and Lineage can be used to predict changes and troubleshoot packages.
8. Contained Databases
Contained Databases is a concept where databases are not tied to the instance they reside on. Everything about a database is contained in that database without today’s coupling to the database engine (SQL Server instance) it’s running under.
Specifically, users are no longer tied to logins on the instance. I’m actually pretty excited about this. I can’t tell you how much time this will save, no longer needing to fix orphaned users in development, test, and stage when we pull down production databases to our non-production environments.
9. Spatial Features
SQL Server spatial features have been greatly expanded with this new release. Highlights include new CircularArc subtypes, support for objects bigger than a logical hemisphere (including a new FULLGLOBE data type), new methods for the geography type, spatial index improvements, Nearest Neighbor Query Plan, and support for persisted computes columns.
I love this Spatial feature are here more info you want to know that, visit for more info:
http://blogs.msdn.com/b/davidlean/archive/2008/11/03/sql-2008-ways-to-architect-spatial-apps.aspx
SQL 2008 Spatial Samples, Part 1 of 9 – How to Learn SQL Spatial
http://blogs.msdn.com/b/davidlean/archive/2008/11/01/sql-2008-spatial-samples-part-1-of-9-how-to-learn-sql-spatial.aspx
SQL Spatial – Tips on Storage
http://blogs.msdn.com/b/davidlean/archive/2008/11/04/sql-spatial-tips-on-storage.aspx
Khan – MCTS – MS SQLDBA, www.addarr.com
MS SQL Server 2011 – Denali
Here are few quick links that you might want to check SQL 2011 – Denali …. efter long time wait for this ….wow
Microsoft(r) SQL Server(r) code-named ‘Denali’ – Community Technology Preview 1 (CTP1)
Download - http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9
Whats New in Denali check this out by your self.
http://msdn.microsoft.com/en-us/library/bb500435(SQL.110).aspx
“HADR” : high-availability and disaster recovery solution, never before.
http://msdn.microsoft.com/en-us/library/ff877884(v=SQL.110).aspx
Columnstore Indexes for Fast Data Warehouse Query Processing in SQL Server 11.0 here you can download pdf format file.
http://download.microsoft.com/download/8/C/1/8C1CE06B-DE2F-40D1-9C5C-3EE521C25CE9/Columnstore%20Indexes%20for%20Fast%20DW%20QP%20SQL%20Server%2011.pdf
Khan – MCTS – SQLDBA. www.addarr.com
List of DBA Paradise DBCC command
Undocumented DBCC command
DBCC activecursors [(spid)]
DBCC addextendedproc (function_name, dll_name)
DBCC addinstance (objectname, instancename)
DBCC adduserobject (name)
DBCC auditevent (eventclass, eventsubclass, success, loginname, rolename, dbusername, loginid)
DBCC autopilot (typeid, dbid, tabid, indid, pages [,flag])
DBCC balancefactor (variance_percent)
DBCC bufcount [(number_of_buffers)]
DBCC buffer ( {‘dbname’ | dbid} [, objid [, number [, printopt={0|1|2} ][, dirty | io | kept | rlock | ioerr | hashed ]]])
DBCC bytes ( startaddress, length )
DBCC cachestats
DBCC callfulltext
DBCC checkalloc [('database_name'[, NOINDEX | REPAIR])] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, ESTIMATEONLY]]
DBCC checkcatalog [('database_name')] [WITH NO_INFOMSGS]
DBCC checkconstraints [( 'tab_name' | tab_id | 'constraint_name' | constraint_id )] [WITH ALL_CONSTRAINTS | ALL_ERRORMSGS]
DBCC checkdb [('database_name'[, NOINDEX | REPAIR])] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, DBCC TABLOCK]
DBCC checkdbts (dbid, newTimestamp)]
DBCC checkfilegroup [( [ {'filegroup_name' | filegroup_id} ] [, NOINDEX] )] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]
DBCC checkident (‘table_name’[, { NORESEED | {RESEED [, new_reseed_value] } } ] )
DBCC checkprimaryfile ( {‘FileName’} [, opt={0|1|2|3} ])
DBCC checktable (‘table_name’[, {NOINDEX | index_id | REPAIR}]) [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]
DBCC cleantable (‘database_name’|database_id, ‘table_name’|table_id, [batch_size])
DBCC cacheprofile [( {actionid} [, bucketid])
DBCC clearspacecaches (‘database_name’|database_id, ‘table_name’|table_id, ‘index_name’|index_id)
DBCC collectstats (on | off)
DBCC concurrencyviolation (reset | display | startlog | stoplog)
DBCC config
DBCC cursorstats ([spid [,'clear']])
DBCC dbinfo [('dbname')]
DBCC dbrecover (dbname [, IgnoreErrors])
DBCC dbreindex (‘table_name’ [, index_name [, fillfactor ]]) [WITH NO_INFOMSGS]
DBCC dbreindexall (db_name/db_id, type_bitmap)
DBCC dbrepair (‘dbname’, DROPDB [, NOINIT])
DBCC dbtable [({'dbname' | dbid})]
DBCC debugbreak
DBCC deleteinstance (objectname, instancename)
DBCC des [( {'dbname' | dbid} [, {'objname' | objid} ])]
DBCC detachdb [( 'dbname' )]
DBCC dropcleanbuffers
DBCC dropextendedproc (function_name)
DBCC dropuserobject (‘object_name’)
DBCC dumptrigger ({‘BREAK’, {0 | 1}} | ‘DISPLAY’ | {‘SET’, exception_number} | {‘CLEAR’, exception_number})
DBCC errorlog
DBCC extentinfo [({'database_name'| dbid | 0} [, {'table_name' | table_id} [, {'index_name' | index_id | -1}]])]
DBCC fileheader [( {'dbname' | dbid} [, fileid])
DBCC fixallocation [({'ADD' | 'REMOVE'}, {'PAGE' | 'SINGLEPAGE' | 'EXTENT' | 'MIXEDEXTENT'}, filenum, pagenum [, objectid, indid])
DBCC flush (‘data’ | ‘log’, dbid)
DBCC flushprocindb (database)
DBCC free dll_name (FREE)
DBCC freeproccache
DBCC freeze_io (db)
DBCC getvalue (name)
DBCC help (‘dbcc_command’ | ‘?’)
DBCC icecapquery (‘dbname’, stored_proc_name [, #_times_to_icecap (-1 infinite, 0 turns off)]) Use ‘dbcc icecapquery (printlist)’ to see list of SP’s to profile. Use ‘dbcc icecapquery (icecapall)’ to profile all SP’s.
DBCC incrementinstance (objectname, countername, instancename, value)
DBCC ind ( { ‘dbname’ | dbid }, { ‘objname’ | objid }, { indid | 0 | -1 | -2 } )
DBCC indexdefrag ({dbid | dbname | 0}, {tableid | tablename}, {indid | indname})
DBCC inputbuffer (spid)
DBCC invalidate_textptr (textptr)
DBCC invalidate_textptr_objid (objid)
DBCC iotrace ( { ‘dbname’ | dbid | 0 | -1 }, { fileid | 0 }, bufsize, [ { numIOs | -1 } [, { timeout (sec) | -1 } [, printopt={ 0 | 1 }]]] )
DBCC latch ( address [, 'owners'] [, 'stackdumps'])
DBCC lock ([{'DUMPTABLE' | 'DUMPSTATS' | 'RESETSTATS' | 'HASH'}]|[{'STALLREPORTTHESHOLD', stallthreshold}])
DBCC lockobjectschema (‘object_name’)
DBCC log ([dbid[,{0|1|2|3|4}[,['lsn','[0x]x:y:z’]|['numrecs',num]|['xdesid','x:y']|['extent','x:y']|['pageid','x:y']|['objid',{x,'y'}]|['logrecs',{'lop'|op}...]|['output',x,['filename','x']]…]]])
DBCC loginfo [({'database_name' | dbid})]
DBCC matview ({‘PERSIST’ | ‘ENDPERSIST’ | ‘FREE’ | ‘USE’ | ‘ENDUSE’})
DBCC memobjlist [(memory object)]
DBCC memorymap
DBCC memorystatus
DBCC memospy
DBCC memusage ([IDS | NAMES], [Number of rows to output])
DBCC monitorevents (‘sink’ [, 'filter-expression'])
DBCC newalloc – please use checkalloc instead
DBCC no_textptr (table_id , max_inline)
DBCC opentran [({'dbname'| dbid})] [WITH TABLERESULTS[,NO_INFOMSGS]]
DBCC outputbuffer (spid)
DBCC page ( {‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ][, cache={0|1} ])
DBCC perflog
DBCC perfmon
DBCC pglinkage (dbid, startfile, startpg, number, printopt={0|1|2}, targetfile, targetpg, order={1|0})
DBCC pintable (database_id, table_id)
DBCC procbuf [({'dbname' | dbid}[, {'objname' | objid}[, nbufs[, printopt = { 0 | 1 } ]]] )]
DBCC proccache
DBCC prtipage (dbid, objid, indexid [, [{{level, 0} | {filenum, pagenum}}] [,printopt]])
DBCC pss [(uid[, spid[, printopt = { 1 | 0 }]] )]
DBCC readpage ({ dbid, ‘dbname’ }, fileid, pageid, formatstr [, printopt = { 0 | 1} ])
DBCC rebuild_log (dbname [, filename])
DBCC renamecolumn (object_name, old_name, new_name)
DBCC resource
DBCC row_lock (dbid, tableid, set) – Not Needed
DBCC ruleoff ({ rulenum | rulestring } [, { rulenum | rulestring } ]+)
DBCC ruleon ( rulenum | rulestring } [, { rulenum | rulestring } ]+)
DBCC setcpuweight (weight)
DBCC setinstance (objectname, countername, instancename, value)
DBCC setioweight (weight)
DBCC show_statistics (‘table_name’, ‘target_name’)
DBCC showcontig (table_id | table_name [, index_id | index_name] [WITH FAST, ALL_INDEXES, TABLERESULTS [,ALL_LEVELS]])
DBCC showdbaffinity
DBCC showfilestats [(file_num)]
DBCC showoffrules
DBCC showonrules
DBCC showtableaffinity (table)
DBCC showtext (‘dbname’, {textpointer | {fileid, pageid, slotid [,option]}})
DBCC showweights
DBCC shrinkdatabase ({dbid | ‘dbname’}, [freespace_percentage [, {NOTRUNCATE | TRUNCATEONLY}]])
DBCC shrinkdb is no longer supported. Please use shrinkdatabase instead
DBCC shrinkfile ({fileid | ‘filename’}, [compress_size [, {NOTRUNCATE | TRUNCATEONLY | EMPTYFILE}]])
DBCC sqlmgrstats
DBCC sqlperf (LOGSPACE)({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]} | {THREADS} | {LOGSPACE})
DBCC stackdump [( {uid[, spid[, ecid]} | {threadId, ‘THREADID’}] )]
DBCC tab ( dbid, objid )
DBCC tape_control {‘query’ | ‘release’}[,('.tape<n>')]
DBCC tec [( uid[, spid[, ecid]] )]
DBCC textall [({'database_name'|database_id}[, 'FULL' | FAST] )]
DBCC textalloc ({‘table_name’|table_id}[, 'FULL' | FAST])
DBCC thaw_io (db)
DBCC traceoff [( tracenum [, tracenum ... ] )]
DBCC traceon [( tracenum [, tracenum ... ] )]
DBCC tracestatus (trace# [, ...trace#])
DBCC unpintable (dbid, table_id)
DBCC updateusage ({‘database_name’| 0} [, 'table_name' [, index_id]]) [WITH [NO_INFOMSGS] [,] COUNT_ROWS]
DBCC upgradedb (db)
DBCC usagegovernor (command, value)
DBCC useplan [(number_of_plan)]
DBCC useroptions
DBCC wakeup (spid)
DBCC writepage ({ dbid, ‘dbname’ }, fileid, pageid, offset, length, data)
Det finns många fall där databasadministratör kontrolleras inte frågor lämnas in av systemet. Som ett resultat är fysisk databasdesign ofta inte inställd så bra som det kunde vara. I ett antal verkliga kund fall där vi undersökte prestandaproblem med dem har vi funnit att detta ofta kan vara ett effektivt sätt att förbättra prestanda.
Några anledningar index är användbara i ett implementerat system:
1.Till före realiseras en sorts över data
2.Till möjliggöra singleton uppslagningar (söker) för särskilda värden
3.Till minska låsning krav som orsakas av scans
4.Till minska minne orsakade av hash går
De två första är mer allmänt förstås fördelar. Du kan undvika ett slags i din plan med 1. och du kan undvika skanningar med 2. Så länge uppdateringen kostnaden för att upprätthålla dessa index är godtagbar, då du kan förbättra urvalsfrågan prestanda med ett index.
Den tredje frågan, låsning, är ett problem i många tillämpningar om vilka det egentligen inte finns mycket skrivet. Vissa program är “bundna” på hur många lås de tar. Ett exempel kan vara en fakturering ansökan med ett antal små frågor som tillgång individ (eller ett litet antal) rader men har många, många sådana förfrågningar samtidigt. Om frågan planerna valt är alla syftar då delad eller exklusiv lås kan endast fattas på raderna för att specifikt nås / förändrats i frågeresultatet. Men om man planerar förändringar som skall en genomsökning i stället för en sökning, kommer antalet slussar öka dramatiskt. Dessa lås kan också hållas längre (beroende på isolering nivå), eftersom frågan kan vara igång längre också. Ett index kan förbättra prestanda för en sådan ansökan, eftersom det förbättrar övergripande fråga genomströmning för många samtidiga användare.
Minne påstående är en annan genomströmning-relaterade problem som kan orsaka problem när ett program körs med många samtidiga anslutningar. I SQL Server, frågor reservera minne för hela sin avrättning. Detta förhindrar att vissa frågor från att inte på en minnesallokering när de har slutfört endast en del av frågan. Om systemet inte har några mer minne att ge, ännu-inte-startade frågor kommer att blockeras och vänta på tidigare frågor för att slutföra (och släppa deras minne reservationer). I vissa applikationer, är systemets totala genomströmningen begränsas av tillgängligt minne som krävs för att möjliggöra trafik som hash går.
Minne påstående är sannolikt ett problem för skalad program som körs på 32-bitars maskinvara som använder mycket minne på toppbelastning. Medan Microsoft Windows har en mekanism som kallas AWE som gör att en process för att använda mer än den normala maximalt minne (vanligen 2 GB), den mekanismen bara är verkligen användbart för att lagra sidor från databasen i minnet. Internt minne konsumenter, såsom Query Optimizer och Query Execution, är begränsade till minne i den virtuella adressrymden (vanligen 2 GB minus det som används för databasen sidbufferten pool och andra interna cachar / minne konsumenter). Detta kan begränsa system som har få index men många frågor som kräver ansluter sig utan stöd index eftersom en hash gå är ofta ett bra val för Optimizer. Dessutom gör plocka optimeringsfunktionen inte olika plan former baserat på belastningen på systemet i SQL Server 2005.
Så kan skapa ett index leda till att systemet plocka slingor ansluter sig eller slå ihop ansluter sig i fall där hash går skulle plockas annars. Skapa ett index eller två på ett sådant system kan minska minne påstående och förbättra systemets totala kapacitet.
SQL Server har länge levereras ett program för att hjälpa till med fysisk databasdesign kallas Index Tuning Wizard (och nu databasoptimera Advisor i SQL Server 2005). Detta kan hjälpa att hitta en någorlunda optimal index som fastställts för en uppsättning frågor. Det fungerar genom att köra Optimizer med en serie av “tänk om” scenarier och utvärdera kostnaden för planen optimeraren plockade i varje fall. Det skäl som sedan om den bästa uppsättning index för att matcha arbetsbördan.
Det finns också en nyare mekanism som du kan använda i SQL Server 2005 som komplimanger den befintliga databasen Tuning Advisor. Det kräver inte att du på förhand identifiera en arbetsbelastning, och det är integrerat i motorn och går som en del av den ordinarie driften av servern (du behöver inte köra något). Det gör inte så mycket arbete som den DTA, men den kan hitta den vanliga problem som orsakar stora prestandaproblem i utplaceras system. Utvecklingen Teamet använde detta för att felsöka ett antal kundapplikationer och fann att det gjorde identifiera “bättre” index i ett antal fall. Om du har att felsöka ett levande system där fysisk databasdesign inte är känd för att vara nära optimala, kan detta vara ett användbart verktyg för att förbättra systemets prestanda.
De särskilda uppgifter om DMVs dokumenteras här:
Jag hoppas att detta ger dig tillräckligt med information för att experimentera med detta. Mer info......
DMV: Dynamic Management Views.
DMVs provide a simple and familiar relational interface for gathering critical system information from your SQL Server. I have found most useful for isolating performance issues. Prerequisites
There are a few things you should know about DMVs before we get started.
A) First you can’t use DMVs without “Dynamic Management Functions” (DMFs). The two are inseparable. Both behave as their names imply. DMVs present the data. DMFs are called when additional processing is needed. Table functions are accessed via the new OUTER/CROSS APPLY operator. There will be two examples of DMFs presented later in this document.
B) It is also important to understand the concept of a “handle”. They play a big role in DMVs. A handle is a SQL Server system generated hash value that takes the place of some large value (text, xml, etc.). The data type for a handle is typically varbinary(64). DMVs use handles instead of the actual text for performance reasons.
The two most widely used handles are:
* sql_handle - Hash value of a sql statement. This value is guaranteed to be unique. As a matter of fact, SQL Server will always generate the same sql_handle on any server for a specific query. This value is extremely useful for comparing query activity across servers using the DMVs described below.
* plan_handle - Hash value of the execution plan for the current sql_handle.
DMV/DMF Overview
I have found most useful. I have also included suggestions on how best to use them.
Query Execution Statistics (DMV) - sys.dm_exec_query_stats
Best Practices
* This view is a much faster alternative to Profiler for isolating performance issues. If you can get exclusive access to an environment, free the SQL Server procedure cache (via DBCC FREEPROCCACHE) then repro the workflow in question. When complete, the results of this DMV will contain aggregated performance statistics for all queries executed. (If you do not have exclusive access, just filter by the “last_execution_time” column).
* It’s generally a good idea to archive the results of this view periodically. Historical data will give you insight into troubleshooting future performance issues. For example, if a query is misbehaving out of the blue, check your historical data to see how it has behaved in the past. Is the query new? If not, is the query being executed more than normal?
* If your database server is suffering from chronic CPU issues, isolate your worst offending queries by querying this view ordered by total_worker_time in descending order. (If the CPU spiked recently then include last_execution_time > [start time of issue], etc.)
* If your query is not showing up in this DMV, its most likely being forced to “recompile” every time. Recompiled queries are never left in cache. To confirm, run a trace against the server with StmtRecompile enabled.
* If you are monitoring a stored procedure that executes multiple sql statements, keep in mind there will be multiple rows returned in this view. SQL Server 2005 breaks up procedure calls into individual SQL statements then caches each separately.
* The Performance Dashboard is a very effective tool to access the data in this DMV (and others). You should be familiar with it and use it whenever you can but you must also be aware of the fact there are limitations with it. It’s designed to handle typical scenarios not all scenarios. This DMV (and others) have more flexibility with accessing the underlying data.
Here are a few examples: 1) in the dashboard you cannot view all queries within specific time ranges. 2) Large SQL strings are hard to read. 3) Data on the screen is a hard to save and analyze at a later date. 4) It’s also hard to compare query activity across servers, etc.
Notes:-
* When a compiled plan is removed from cache, the rows corresponding to query plan are deleted from the DMV.
* This DMV is updated only after the query execution is completed successfully.
Sample Queries
The following query pulls the top 5 worst CPU consuming queries on the SQL Server (since the last time the server was restarted)
SELECT TOP 5
sql.text as sql
, qp.query_plan
, creation_time
, last_execution_time
, execution_count
, (total_worker_time / execution_count) as avg_cpu
, total_worker_time as total_cpu
, last_worker_time as last_cpu
, min_worker_time as min_cpu
, max_worker_time as max_cpu
, (total_physical_reads + total_logical_reads) as total_reads
, (max_physical_reads + max_logical_reads) as max_reads
, (total_physical_reads + total_logical_reads) / execution_count as avg_reads
, max_elapsed_time as max_duration
, total_elapsed_time as total_duration
, ((total_elapsed_time / execution_count)) / 1000000 as avg_duration_sec
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
CROSS APPLY sys.dm_exec_query_plan (plan_handle) qp
ORDER BY qs. total_worker_time DESC
*Note: dm_exec_sql_text & dm_exec_query_plan will be discussed in more detail in the next sections
SQL Text (DMF) - sys.dm_exec_sql_text
As mentioned above, for performance reasons DMVs refer to SQL statements by their “sql_handle” only. Use this function if you need to retrieve the SQL text for a given handle. This DMF accepts either the sql_handle or plan_handle as input criteria.
* This DMF (and others) will not work in a database with 8.0 (SQL Server 2000) compatibility enabled. To get around this, execute your query from a system database (such as master) then refer to the DMF using a three-part name. For example: CROSS APPLY yourdatabase. sys.dm_exec_sql_text(plan_handle).
Sample Queries
* Examples of this DMF can be found in the “Query Execution Statistics” and “Requests” sections.
Query Execution Plan (DMF) - sys.dm_exec_query_plan
This DMF returns the Showplan (or “execution plan”) in XML format for the batch specified by the plan handle. The plan specified by the plan handle can either be cached or currently executing.
* Execution plans in XML format are hard to read. To view graphically in “Management Studio”, do the following: 1) When returning data from this function, SQL Server automatically creates a hyperlink for the showplan XML, 2) Click hyperlink to bring up the document in the Internet Explorer, 3) Do File-Save As to your local drive with a .SQLPLAN file extension, 4) Go back to SQL Management Studio and open document saved above.
* SQL Server builds the query plan for a query based on the parameters entered during the execution of the first query. This is commonly known as “Parameter Sniffing”. All subsequent requests use this plan regardless of their own parameter combinations. A bad combination of parameters for the first execution can cause issues with subsequent requests. The parameters used to “seed” the execution plan are saved in the execution plan XML. This data can be extremely helpful if you suspect a query is generating different query plans depending on the initial criteria entered. If you find one set of parameters work better than another, create an “OPTIMIZE FOR” plan guide to force this parameter combination.
* It is a good idea to archive the execution plan xml from your most heavily used queries every month or two. This data can be retrieved if performance goes sour at a later date. This historical data will allow you to do side-by-side comparisons of query plans before and after the event enabling you to quickly pinpoint the cause. Worse comes to worse, if you are unable to regenerate the old plan again, there is always the option of forcing the old saved plan via a plan guide, especially in SQL 2008.
* Generating the execution plan XML is a fairly CPU intensive process. Tread lightly when using this function on production servers. For example, keep query results below 100-500 records, etc.
Sample Queries
* Examples of this DMF can be found in the “Query Execution Statistics” section.
Missing Indexes (DMV/DMF) - sys.dm_db_missing_index_details
When SQL Server generates a query plan, it analyzes what are the best indexes for a particular filter condition. If these indexes do not exist, the query optimizer generates a suboptimal query plan then stores information about the optimal indexes for later retrieval. The missing indexes feature enables you to access this data so you can decide whether these optimal indexes should be implemented at a later date.
Here’s a quick overview of the different Missing Index DMVs/DMFs
- sys.dm_db_missing_index_details (DMV) – Returns indexes the optimizer considers are missing.
- sys.dm_db_missing_index_columns (DMF) - Returns the columns for a missing index.
- sys.dm_db_missing_index_group_stats (DMV) - Returns usage and access details for the missing indexes similar to sys.dm_db_index_usage_stats .
* SQL Server is not that smart. Never blindly implement indexes suggested in this view. Before implementing, create recommended indexes on a test server then do your best to simulate a typical workload (via a trace replay, etc). You should confirm new indexes are being used and with positive results. If you introduce an index that is not being used, you are wasting disk space and more importantly you are potentially increasing compile time for all queries against the table in question.
* These DMVs will take a while to be populated. You should check these views only after your server has been up and running for a while. If this view is empty, you're not missing any indexes that are obvious enough for the SQL Server to detect.
Sample Query
The following query returns a prioritized list of the missing indexes in the current database.
SELECT so.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) as Impact
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
INNER JOIN sys.objects so WITH (nolock) ON mid.object_id = so.object_id
WHERE migs.group_handle IN (
SELECT TOP (5000) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC)
Index Usage Statistics (DMV) - sys.dm_db_index_usage_stats
This view reports aggregated totals for all indexes in the current database. With this view, we not only know if its being used but how it’s being used. We now know the number of times a index has been involved in a seek (user_seeks), a scan (user_scans ),or a bookmark lookup (user_lookups) .
Best Practices:-
* An index is not being used and can be removed from the server if its corresponding user_seeks + user_scans + user_lookups = 0. Note you should never blindly drop indexes without testing first. For example all indexes have statistics behind them. It is possible the query optimizer is still using the statistics behind your unused index and not the index itself.
* Removing unused indexes could be a large performance improvement. 1) Indexes slow down inserts/updates/deletes. 2) Indexes slow down compile times for all queries accessing the table in question. 3) SQL Server will make a best guess for an execution plan after a certain period of time. If you have too many indexes on a table, you are increasing the chances for the optimizer to give up before reaching the optimal index.
The higher the value, the higher the priority to rebuild and/or reorganize.
* Like most DMVs, the data is lost after SQL is restarted. It’s a good idea to periodically archive this DMV.
Sample Queries
A) This query finds all unused indexes in the current database.
select t.name as TableName
, i.name as IndexName
from sys.indexes i
inner join sys.dm_db_index_usage_stats s on s.object_id = i.object_id and s.index_id = i.index_id
inner join sys.tables t on i.object_id = t.object_id
where ((user_seeks = 0 and user_scans = 0 and user_lookups = 0) or s.object_id is null)
B) This query builds a list of indexes that could benefit from ALTER INDEX REBUILD or ALTER INDEX REGORGANIZE.
select t.name as TableName
, i.name as IndexName
from sys.indexes i
inner join sys.dm_db_index_usage_stats s on s.object_id = i.object_id and s.index_id = i.index_id
inner join sys.tables t on i.object_id = t.object_id
inner join #frag_indexes f on i.object_id = f.object_id and i.index_id = f.index_id
order by s.user_scans desc, f.priority asc
A Note #frag_indexes was generated/prioritized above based on data from sys.dm_db_index_physical_stats. Code was not included for readability.
System Requests (DMV) - sys.dm_exec_requests
Overview
The System Requests DMV displays information regarding each request occurring on the SQL Server. This view combined with the sessions DMV (sys.dm_exec_sessions) are basically a “selectable” version of “sp_who2” with a lot more columns to choose from and of course a lot more control over the records you are viewing.
The percent_complete & estimated_completion_time columns in this view will allow you to estimate the completion times for the following events: ALTER INDEX REORGANIZE, ROLLBACK, BACKUP DATABASE , DBCC CHECKDB, DBCC SHRINKDATABASE, and DBCC SHRINKFILE.
* For users who want more control over block monitoring, this view is good alternative to sp_blocker and sp_who2. Requests are blocked by the SPIDs identified in the blocking_session_id column. However, you must be careful joining blocking_session_id back to sys.dm_exec_requests. It is possible for a record to be blocked by a SPID that no longer exists in sys.dm_exec_requests. For example, if the blocking query is complete but a transaction is left open, etc. Sample Queries
A) This query finds all active queries with estimated completion time (if available)
select e.session_id
, sql.text
, e.start_time
, s.login_name
, s.nt_user_name
, e.percent_complete
, e.estimated_completion_time
from sys.dm_exec_requests e
join sys.dm_exec_sessions s on e.session_id = s.session_id
cross apply sys.dm_exec_sql_text(plan_handle) sql
where e.status = 'running'
B) This query finds all active queries with estimated completion time (if available)
select er.session_id as spid
, sql.text as sql
, er.blocking_session_id as block_spid
, case when erb.session_id is null then 'unknown' else sqlb.text end as block_sql
, er.wait_type
, (er.wait_time / 1000) as wait_time_sec
FROM sys.dm_exec_requests er
LEFT OUTER JOIN sys.dm_exec_requests erb on er.blocking_session_id = erb.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) sql
CROSS APPLY sys.dm_exec_sql_text(isnull(erb.sql_handle,er.sql_handle)) sqlb
where er.blocking_session_id > 0
* DBCC SQLPERF(logspace)
DBCC SQLPERF -logspace - is an absolutely functional command if you are only interested in consumption of your database log files. It provides the cumulative size for each log file for each database on the SQL Server instance as well as the amount of space consumed -as a percentage of total log file size the results are an aggregate for the database logspace can be 1 of the value below:
UMSStats – SQL thread management
WaitStats – resources, wait types
IOStats – outstanding reads & writes (note: always zeros)
RAStats – read ahead activity (note: always returns zeros)
Threads – I/O / CPU / memory usage per thread
SpinLockStats – statistics on spinlocks
UMSSpinStats – statistics on UMS
NetStats – ODS statistics
LRUStats – LRU-MRU chain statistics (Note: free page scan always zero).
* SQL @@ROWCOUNT
Returns the number of rows affected by the last statement. It will let you to do a checking on the record you updated. If the number of rows is more than 2 billion, use ROWCOUNT_BIG. Example
USE DB2008;
GO
UPDATE User
SET JobTitle = ‘Manager’
WHERE UserID = ‘u10021′
IF @@ROWCOUNT = 0
PRINT ‘Warning: No rows were updated’;
GO
Ten Common Database Design Mistakes:
So, the list:
1. Poor design/planning
2. Ignoring normalization
3. Poor naming standards
4. Lack of documentation
5. One table to hold all domain values
6. Using identity/guid columns as your only key
7. Not using SQL facilities to protect data integrity
8. Not using stored procedures to access data
9. Trying to build generic objects
10. Lack of testing.
MS SQL Server 2008:
1) Data Mining with Microsoft SQL Server 2008 from Microsoft MCTS Course.
Exercise 1: Creating Data Mining Models. In this exercise, you will develop an Analysis Services solution by using the Microsoft Business Intelligence Development Studio environment. Business Intelligence Development Studio is an environment based on the Microsoft Visual Studio environment. Business Intelligence Development Studio provides an integrated development environment for designing, testing, editing, and deploying projects to an Analysis Services instance. You will create and view a data mining structure with Decision Trees and Naive Bayes data mining models..... Download
2) Microsoft SQL Server 2008 Analysis Services: Pervasive Analysis through 2007 Microsoft Office Excel.
Exercise 1: Working with Analysis Services Data through Microsoft Excel 2007
In this exercise, you will use Microsoft Office Excel 2007 to browse and analyze your data stored in Analysis Services. You will use both Microsoft Office Excel PivotTable and Microsoft Office Excel PivotChart to provide both numerical and graphical representations of your data. You will also explore how adding Key Performance Indicators (KPI) within your PivotTables can help users to better visualize the trends within your data. You will further expand the reach of your reports by adding a drillthrough action to enable key decision-makers to see additional properties associated with your data. Finally, you will use a perspective to better focus users on the data specific to the tasks they are performing......Download
3) Partition Processing for Data Warehousing
Exercise 1: Creating a Partitioned Table
In this exercise, you will create a partitioned data warehouse fact table. Very large tables often require data to be stored over several disk volumes. SQL Server tables cannot be placed in specific files. However, filegroups can be placed on files and tables can be assigned to filegroups. This enables you to control the storage of data in very large tables in SQL Server. Furthermore, if a table spans several filegroups, it can be useful to define which data is placed on which filegroup. Partitioning functions provide this functionality by horizontally splitting tables based on the value in a particular column. .....Download
4) Plan Freezing
Exercise 1: Preventing Query Plan Regression. In this exercise, you will create a query plan that you can use if performance is affected by a SQL Server upgrade. During an upgrade of SQL Server query plans are often updated and, although most plan changes are beneficial, some plan changes cause performance degradation. To prevent this problem, plan guides can be stored for mission-critical queries. To enable performance analysis after upgrades, the plan guides are typically disabled. If performance is degraded, you can enable the plan guides to return to the previous query plan. ..... Download.
Resource Governor
Exercise 1: Enable Resource Governor
In this exercise, you will enable resource governor and use System Monitor and system views to monitor resource usage. Resource contention is a common source of performance bottlenecks. Important applications are often starved of resources by less important resource-intensive applications. Careful monitoring is required to ensure that important applications achieve required performance levels. ....Download.
Using Full Text Search in SQL Server 2008:
SQL Server 2008 Full-Text Search feature can be used by application developers to execute full-text search queries against character based data residing in a SQL Server table. To use full text search the developer must create a full-text index for the table against which they want to run full-text search queries. For a particular SQL Server Table or Indexed View you can create a maximum of one Full-Text Index. The full-text index can be created for columns which use any of the following data types - CHAR, NCHAR, VARCHAR, NVARCHAR, TEXT, NTEXT, VARBINARY, VARBINARY (MAX), IMAGE and XML.
Each full-text index can be used to index one or more columns from the base table, and each column can have a specific language which is supported by SQL Server 2008 Full-Text Search. Full-Text Search in SQL Server 2008 supports more than 50 different languages such as Arabic, Chinese, English, Japanese and Spanish etc.
For the complete list of supported full-text languages, run the below TSQL query.
Use master
GO
SELECT * FROM sys.fulltext_languages ORDER BY name ASC
GO
Installing Full-Text Search
The Full-Text Search feature of SQL Server 2008 is an optional component the Database Engine and as a result this feature is not installed by default. During SQL Server 2008 Installation, the database administrator needs to select the Full-Text Search feature as shown.
| 
Tutorials 
