Category / Uncategorized

by Arvind Shyamsundar

Connectivity related questions

Some time back there were some connectivity related questions thrown at me:


Q1. We know about the Dedicated Admin Connection (DAC) and how it is useful for troubleshooting service hang issues. We also know that DAC should only be used when necessary. Can we find out if someone is misusing the DAC and staying connected all the time?


A. You can use the following query from a normal query window to check:


select * from sys.dm_exec_sessions


where endpoint_id IN


(


        select endpoint_id from sys.tcp_endpoints where is_admin_endpoint = 1


)


Do note that this assumes that the service is working normally and that you can connect normally đź™‚


Q2. “I have disabled the named pipes protocol on server but I still find the server ‘listening’ on named pipes. See errorlog snippet below for what I mean”:



2007-05-23 11:36:56.69 Server      Server local connection provider is ready to accept connection on [ \.pipeSQLLocalINSTANCE1 ].



2007-05-23 11:36:56.69 Server      Server local connection provider is ready to accept connection on [ \.pipeMSSQL$INSTANCE1sqlquery ].


The “local connection provider” refers to the shared memory protocol. If named pipes is genuinely enabled, you will receive an entry similar to: 


2006-05-09 07:37:12.57 Server Server named pipe provider is ready to accept connection on [ \.pipeMSSQL$INSTANCE1sqlquery ].


So, do not worry about it – named pipes is not enabled 🙂


Q3. “I have configured a fixed TCP port for SQL to listen to but I still find entries in the errorlog which seem to indicate dynamic ports being used:”



2007-05-23 12:27:42.84 Server      Server is listening on [ 127.0.0.1 <ipv4> 1115].


To disable dynamic ports, use a blank value (remove the 0) from the Dynamic Port entry. However, you would still receive such an entry for DAC like this:


2007-06-15 17:03:16.63 Server      Server is listening on [ 127.0.0.1 <ipv4> 1509].


2007-06-15 17:03:16.63 Server      Dedicated admin connection support was established for listening locally on port 1509.


Hope these answers are useful!

by Arvind Shyamsundar

The role of STOPAT in RESTORE DATABASE

Previously in SQL Server 2000, the STOPAT clause was provided for RESTORE LOG wherein you can “halt” the recovery process at a particular point in time. This feature is very useful for recovering from accidental user errors and such.


Now in SQL Server 2005, there is a STOPAT clause provided in the RESTORE DATABASE command also. The first time I saw this option, I was confused and wondered how we can provide a point-in-time recovery with a full database backup – because the full database backup is essentially an image of the database and does not contain the entire log backup (only contains the portion which changed during the backup command itself.) But I never thought about it much after that.


So when a customer asked me how this feature works, I dug in a little bit and here’s what I found:



  • Without log backups, there is simply no way to achieve a true point-in-time restore

  • What we do when we specify STOPAT in RESTORE DATABASE is to test if the full database backup is already ahead of the point in time you want to stop at. If it is, then the command errors out.

  • Why it errors out is to provide you a clear signal that even if you restore this full backup and then restore subsequent transaction log backups on top (specifying a STOPAT) then those transaction log backups would fail with errors since the database is already rolled forward to a point in time ahead of the STOPAT

  • In the above case, you would have wasted a lot of time and effort first restoring the database and then the transaction log backup before finding out that you should have started with a earlier full backup.

So, STOPAT in RESTORE DATABASE can be a little disappointing if you are not aware of the real purpose of this clause. Please review http://msdn2.microsoft.com/en-us/library/ms191468(SQL.90).aspx which explains this in detail.

by Arvind Shyamsundar

Enforcing password policy for all SQL Authentication Logins

One of my customers recently asked me how we can enforce the CHECK_POLICY option for all logins created on the SQL 2005 instance. Since by default this is enforced on a per-login basis, we cannot by default have it at a server level. But by using DDL triggers in SQL Server 2005, here is one way of achieving it:


—- BEGIN DDL Trigger Code —  


CREATE TRIGGER trgNewLogin on all server
for create_login
as
 declare @loginbeingcreated sysname
 select @loginbeingcreated = eventdata().value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘sysname’)


 If EXISTS
 (
  select 1 from sys.sql_logins
  where name = @loginbeingcreated
  and is_policy_checked = 0
 )
 BEGIN
  Print ‘Please ensure password policy is being checked by using CHECK_POLICY’
  ROLLBACK
 END


— END Trigger Code —


 

by Arvind Shyamsundar

Symbols and symbol files

Some of us are unaware of the relevance of proper symbols files. The symbol file is a deliverable just like the binary. Though it is not required for running the application, it is very important to debug issues which arise in production.


 


What are symbols and symbol files?


Symbols contain the mapping between the compiler-generated machine code and your source code. They help the debugger to “understand” the addresses of functions, parameters and variables and map them back to source code references. Without a proper symbol file, the debugger may present disassembly output which looks like this:


 


            call YourApp+0x11c0d (00411c0d)


 


With proper symbol resolution, the debugger can correctly interpret this machine code as the equivalent function name:


 


            call YourApp!CSampleDlg::OnBtnClicked


 


In the Visual Studio family of products, we use the PDB (Program Database) format for encoding the symbolic information into a file. Given below are the steps to generate the symbol files for various product versions. Please ensure that the PDB files generated are of the same code base as the service EXE/DLLs that are actually used in the testing. Everytime your code is rebuilt, the PDB is re-generated and has to be maintained in sync with your code base and binaries.


 


Private vs. Public symbols


Public symbol files are what you get from Microsoft for the Windows OS and other products. You can obtain public symbols for Microsoft products either by referencing the public symbol server at http://msdl.microsoft.com/download/symbols, or by downloading appropriate symbol packages from http://www.microsoft.com/whdc/devtools/debugging/default.mspx. Public symbols normally just contain:




  • Mapping from addresses to function names



  • Frame pointer omission (FPO) records


Private symbol files are the default kind of files generated by the Visual Studio linkers. These files contain the following details in addition to what the public symbols contain:




  • Line numbers and source file information



  • Parameter names and data types



  • Local variable names and data types


Normally you would want private symbol files for any detailed debugging of your own application. However, it may not be a very good idea to share your private PDB files with your end customers, since some of the information in the private PDB can be viewed as intellectual property.


 


To generate stripped / public PDB files with Visual C++, use the information in http://msdn2.microsoft.com/en-US/library/y87kw2fd.aspx for steps.


 


How to generate symbol files for Visual C++.NET 7/7.1/8.0


In these versions of VC++, a symbol file should automatically be created if you are compiling a debug build. If you are compiling a release build, then you will need to check the steps below to ensure that linker will create symbols. Please perform the symbol generation steps for each and every module in your application. If it loads other DLLs then these steps need to be done for those DLLs also.




  • Open the source code for your project.



  • Open the properties page for the project from Solution Explorer



  • View the Configuration Properties->Linker->Debugging node



  • Make sure “Generate Debug Info” is set to YES



  • Also check that the “Generate Program Database File” is set to a non-blank path



  • Rebuild the project to generate the PDB file


How to generate symbol files for Visual C#.NET 7/7.1/8.0


In VC#, a symbol file should automatically be created if you are compiling a debug build. If you are compiling a release build, the steps are given below.




  • Open the source code for your project.



  • Open the properties page for the project from Solution Explorer



  • View Configuration Properties->Build node



  • Make sure “Generate Debugging Information” is set to True.



  • Rebuild the project to generate the PDB file


How to generate symbol files for Visual Basic.NET 7/7.1/8.0


In VB.NET, a symbol file should automatically be created if you are compiling a debug build. If you are compiling a release build, the steps are given below.




  • Open the source code for your project.



  • Open the properties page for the project from Solution Explorer



  • View Configuration Properties->Build node



  • Make sure “Generate Debugging Information” is checked.



  • Rebuild the project to generate the PDB file


How to generate symbol files for Visual C++ 6.0


Important note: if you are still using VC++ 6.0, be aware that Microsoft Support will not be in a position to support you on issues concerning the product. That is because the end of support lifecycle has been reached for this version of VC++. For more information, review the details at http://support.microsoft.com/lifecycle and plan to upgrade to higher versions of VC++.NET. The information below is provided for reference only, and should not be interpreted as a sign of support!


 


In VC++ 6.0, a symbol file should automatically be created if you are compiling a debug build. If you are compiling a release build, then you will need to follow these steps to create symbols. Please perform the symbol generation steps for each and every module in your application. If it loads other DLLs then these steps need to be done for those DLLs also.




  • Open the source code for your project.



  • Select Settings from the Project menu.



  • In the Link tab, select General from the Category drop-down.



  • Check Generate Debug Info



  • In the C/C++ tab, select General from the Category drop-down.



  • Select Program Database from the Debug Info drop-down.



  • Link with /RELEASE to get a non-zero checksum.



  • Go to the Category drop-down & select Listing Files.



  • In the Listing File drop-down, select ‘Assembly, Machine Code, and Source’.



  • Rebuild your project.


How to generate symbol files for Visual Basic 6.0


In VB6, symbols are not created by default. To create symbols for your VB6 applications, follow these steps:




  • Open the source code for your project.



  • Select “<projectname> Properties” from the Project menu.



  • In the Compile tab, check Create Symbolic Debug Info and select No Optimizations



  • Recompile. This will create a .PDB file (the symbols) in the same directory as your .DLL / .EXE / .OCX file.



  • Note: This does *not* break binary compatibility.


 

by Arvind Shyamsundar

Resources from TechEd sessions

Several attendees have asked for the resources section from our presentations at TechEd India. Enjoy!


Blogs
SQLCAT: http://blogs.msdn.com/sqlcat
WINCAT: http://blogs.msdn.com/wincat
Slava Oks’ blog: http://blogs.msdn.com/slavao


Product Feedback
http://lab.msdn.microsoft.com/productfeedback/


External Links:
NUMA FAQ: http://lse.sourceforge.net/numa/faq


Books:
Inside Microsoft SQL Server 2005: Query Processing and Optimization (Volume 3)
The Guru’s Guide to SQL Server Architecture and Internals (SQL Server 2000)


SQL Server 2005 – Operations 
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx


Whitepapers
Troubleshooting Performance Problems in SQL Server 2005 (http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx)
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005(http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx)
Statistics Used by the Query Optimizer in SQL Server 2005 (http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx)
SQL Server 2000 I/O Basics (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx)


Webcasts
Troubleshooting Performance Problems In SQL Server 2005 (http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032275646&EventCategory=5&culture=en-US&CountryCode=US)
Performance Diagnosis in SQL Server 2005 (http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032275586&EventCategory=5&culture=en-US&CountryCode=US)
Supportability features for SQL Server 2005
(http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032275593&EventCategory=5&culture=en-US&CountryCode=US)


PASS 2005 PSS Service Center Labs
http://www.microsoft.com/downloads/details.aspx?FamilyID=9b8098a7-e75f-462a-b296-e80199c9f323&displaylang=en

by Arvind Shyamsundar

Wait types in SQL Server

Troubleshooting performance issues in SQL Server involves studying the wait types and wait times associated with the queries executing. However, the relevance of each of the various wait types in the output can be a mystery.


For SQL Server 2000, the cumulative wait types can be observed using the DBCC SQLPERF(WAITSTATS) command. To clear the current wait times, use DBCC SQLPERF(WAITSTATS, CLEAR). The KB article The waittype and lastwaittype columns in the sysprocesses table in SQL Server 2000 explains the more common ones. A more comprehensive reference is found in Tom Davidson’s excellent article Opening Microsoft’s Performance-Tuning Toolbox. The article’s code download has an extremely useful table which details almost all the wait types and explains their correlation with the performance counters.


For SQL Server 2005, we have a good reference in SQL Books Online itself, as part of the sys.dm_os_wait_stats reference documentation. And if you haven’t read it yet, don’t waste a minute and read the excellent Troubleshooting Performance Problems in SQL Server 2005 white paper.

by Arvind Shyamsundar

Database Snapshots

DB Snapshots are a very useful new feature in SQL Server 2005 Enterprise Edition. They provide us the ability to preserve a consistent picture of the database as it was at a point in time. Both metadata and data are captured in the snapshot.


 


The main uses of snapshots are for preserving views of the database for reporting purposes, and also to easily recover data affected by user error. Snapshots also provide a means to view the data in the mirror database.


 


DB Snapshots use “sparse files”, which means that the actual snapshot size on disk will be very small initially and would only grow by recording the the changes to the database using a copy-on-write scheme.


 


Another cool feature of snapshots for recovering from administrative or user error is the ability to revert a database back to a snapshot. This is potentially much quicker than restoring a full backup, as we only copy the changed pages from the snapshot back into the data files. But there are other implications of reverting to a snapshot – most important being the fact that we rebuild the log in such cases. To ensure recoverability of all transactions committed after the snapshot was taken, be sure to backup the log prior to using this feature.