First of all, welcome to the light, we’ve been waiting for you. Just kidding…(sorta).

The first question you need to answer is what’s been causing you problems? Have you taken a look at Microsoft’s Guide to Migrating from Sybase ASE to SQL Server 2005 whitepaper? The planning phase is critical in ensuring your migration is successful. You should definitely do some due dilligence with regard to equivalent, nonsupported and emulated functions as well as unique datatype conversions, and syntax differences/disagreements to determine what’s going to create problems.

There is certainly no shortage of third-party applications to help you migrate your databases and applications, but this can be the perfect opportunity to gain some deep insight into your environment. Yes, that is a glass-half-full way of looking at it, but it also happens to be true. A third-party application can help, but if something during the migration goes south, you or the vendor’s support are still going to have to investigate your environment to determine what went wrong and why. Wouldn’t you rather know?

Personally, I’m a big fan of testimonials and the type of information you usually find in forums and Google groups. In an article entitled Migrating from Sybase to SQL Server Sayed Geneidy talks about his experience migrating to SQL Server 2000. Look at the sections on data compatibility mode and optimizer hints for some items that frequently cause people headaches during a migration.

Hope that helps!

You’re looking to run a SQL Server profiler trace. Performing a trace is Microsoft’s means of capturing every statement executed against an instance (or specified database(s)), but be aware that this level of information comes with a performance penalty inherent in capturing everything.

Now this is by no means the only way to get what you’re looking for. You could also write a script or a set of scripts leveraging a number of different T-SQL syntaxes/commands/functions to gather this information, although nothing guarantees you’ll see everything like profiler does. Here are three options:

1. For SQL Server 2005 you can use the sys.dm_exec_requests DMV and CROSS APPLY the sys.dm_exec_sql_text function in a loop. The MSDN link for sys.dm_exec_sql_text includes a few sample scripts to illustrate how you can leverage that interaction.
2. For SQL Server 2000 (and 2005 if you’d like) you can use the fn_get_sql function together with the sys.sysprocesses system view to see SQL Sytnaxes and to correlate them with current session information.
3. Using DBCC INPUTBUFFER together with sysprocesses (or sys.dm_exec_requests) in a loop can provide similar information to fn_get_sql.

Note that both fn_get_sql and DBCC INPUTBUFFER are limited by the amount of text that can be returned, so don’t consider these an end-all-be-all solution for SQL monitoring.

If you don’t want to get into writing all the scripts you’ll need, or you don’t have the time and resources to create a process that will maintain the databases, tables, reports, and other related monitoring data there is certainly no shortage of third-party applications vying for your attention.  These products specialize in providing user-friendly, formatted, guided answers and advice to important questions like:

• How long does that statement usually run?
• Who usually runs that statement?
• Has the plan for that statement changed? If so, when and why and who changed it?
• …and the list goes on and on.

Once you start asking those questions, SQLServerPedia can certainly be your resource to get answers, and Quest has an arsenal of tools to help analyze, diagnose and resolve even your most complicated performance or management issues.  Hope to see you back here soon!

There is definitely no shortage of SQL Server information online, but when you’re looking for a specific answer, it helps to know you can trust the information you’re reading. Here’s Paul Randal’s blogroll of SQL Server team members, MVPs and other trustworthy sources you can add to your list of trusted sites to help you get the reliable answers you’re looking for.

This is a really tough problem for people to troubleshoot because there is little useful information available about non-I/O latches.

First things first, what are latches? Latches are lightweight synchronization locks used to coordinate access to data either on disk (PAGEIOLATCH) or in the bufferpool (LATCH, PAGELATCH); the latter is actually a bit of a gray area as data pages in the bufferpool are still tied to pages on disk. So why not call them locks to save confusion? Well, because they’re different. For one, lightweight is usually interpreted as short-lived, however there’s more to it than that. Latches are lightweight because users cannot directly control them (people will argue with this statement, but for the purposes of this short article I’ll leave it at that), information on the latch owner is not fully (read always) available, and latches are granted differently than locks. For a very thorough review of exactly what is unique about latches and how to troubleshoot latching issues, Ken Henderson published the best information I’ve seen to date on waits and blocking issues entitled SQL Server 2005 Waiting and Blocking Issues.

I’ll admit the aforementioned article can be a tough read - especially if you’re looking for summary information, or you don’t have a lot of background in SQL Server. So, in summary:

• There are four main causes of page latch blocking:
  1. IO subsystem performance
  2. Contention on internal allocation system tables
  3. Contention on catalog pages
  4. Contention on data pages
• You will never see a latch wait time greater than 5 minutes (something else that makes latches unique from blocking locks)
• Latches are granted using the First-In-First-Out (FIFO) method
• User table latch contention is the simplest to troubleshoot, and is typically the most common source of confusion with latch contention issues. Frequent modifications to the data in specific tables can create hot spots in a database, or more specifially hot pages. This isn’t a symptom of frequent SELECT operations, but rather INSERTs, UPDATEs, and DELETEs.
• When troubleshooting a latching issue:
  1. Check the latch class description in Books Online, if available.
  2. Examine the latch class for indications of what components or what type of statements may acquire the latch.
  3. Examine the blocked task’s current statement for more hints regarding the latch usage.
  4. Use the suffix of the wait type to determine the mode for the blocked request. This will help identify whether the latch is being acquired for shared access—in which case, it must currently be held for exclusive access. An exclusive request implies that the blocked task’s current session needs to do some update, and this can provide further info regarding resolving the blocking.
  5. Query the following DMVs:
     1. sys.dm_os_wait_stats
     2. sys.dm_os_latch_stats
     3. sys.dm_exec_requests
     4. sys.dm_tran_active_transactions

So, to get to the original question: what are you reading incorrectly? Nothing. In your case you had (screenshots not included since I’m not that well-heeled a blogger yet) two SPIDS, both exhibiting parallel coordination (CXPACKET), shared (PAGELATCH_SH) and update latches (PAGELATCH_UP). Since you were observing queries in a parallel environment and simultaneously saw thread coordination waits, once the threads were complete, the latch was removed and the blocking went away; because of the FIFO behavior of latches, one incompatible latch type will cause the rest to wait. Since SQL Server 2000 SP4, Microsoft’s been identifying latches as the cause of blocking in sysprocesses, however since there is no owner information for certain lock types, the DMVs in SQL Server 2005 will not show you a blocker (for example, in sys.dm_os_waiting_tasks) when the blocking latch type is a SH latch.

For more information on troubleshooting performance problems in SQL Server 2005, read the aptly named Microsoft whitepaper Troubleshooting Performance Problems in SQL Server 2005.

Page Life Expectancy can tell you whether your SQL Server has enough memory for its bufferpool to function properly. Extremely low Page Life Expectancy values can be an indication of:

• Too little memory on the system
• Too little memory configured for SQL Server’s use
• Poorly written application code

Brian Moran wrote an article for SQL Server magazine on this back in 2004 entitled Page Life Expectancy a Reliable Indicator of SQL Server Memory Pressure that sums up how you can use Page Life Expectancy to determine whether adding memory is warranted, and things haven’t changed since he wrote the article. Essentially, you might find that there is too little memory installed on the server to enable SQL Server to function properly or that SQL Server hasn’t been configured to use enough of what’s available.

Still, you can throw memory at a server all day long (memory’s cheap, right?) but if your application code is behaving badly it might not help. While you might see a temporary improvement, you’ll likely start running into other issues as the application scales. By addressing application-specific problems like stored procedures that are recompiled due to:

• Underlying schema changes
• Statistics changes
• Including SET options in the procedure definition
• Temporary table changes
• Use of the RECOMPILE query hint or OPTION (RECOMPILE)
• Including data definition language (DDL)

Or by addressing queries that read an entire table into the bufferpool to modify a small number of rows, you can save lots of arguments with systems administrators and managers about adding memory, and improve your application’s performance at the same time. Read Microsoft’s technical document Troubleshooting Performance Problems in SQL Server 2005 for details on these and other tips for improving the performance of your SQL Server, and the Microsoft TechNet article Top SQL Server 2005 Performance Issues for OLTP Applications for OLTP-specific tuning tips.

I have been on a number of support calls where the customer insists there is a CPU bottleneck, and in some cases he/she even points to my blog posts and/or those from others indicating that SQL Server scheduler yields are an indication of CPU pressure; however perfmon’s OS CPU counters don’t support that observation. Once we get to the root of the issue and resolve the problem, the customer (very rightly) wants an explanation of why there were scheduler yields (SOS_SCHEDULER_YIELD) without any noticeable CPU pressure.

In some recent cases I have pointed customers to Mario Broodbakker’s excellent article Taking the Guesswork out of SQL Server Performance Profiling Part 2 (Tom Davidson wrote a related albeit shorter article). Mario explains some important concepts relating to the SQL Server Scheduler and monitoring performance data, taking time to explain key concepts, and using clear examples to illustrate his points. The links to other blogs/sites/whitepapers provide good background reading to either familiarize the reader, or help him/her to brush up on related information.

In essence, the article illustrates what can happen when two “killer” queries are executed on the same scheduler. Because the SQLOS attempts to ensure that no one query will monopolize a CPU queries are forced to yield when appropriate.

For background and sample scripts relating to the SQL Server Scheduler you can look at Slava Oks’s posts on SQLOS - unleashed and SQLOS’s DMVs Continued which benefit from first understanding the user mode scheduler (UMS) which was best explained by the late (and great) Ken Henderson (see Inside the SQL Server 2000 User Mode Scheduler).

Mario points out how:

• when CPU-intensive queries are assigned to different schedulers yields are not observed, however when multiple CPU-intensive queries are assigned to the same scheduler they cause one another to yield thereby producing a situation that appears as CPU Pressure but is in fact what he refers to as SOS Scheduler pressure
• when the overall CPU load on the system is high a similar phenomenon is observed, however not only are scheduler yields increased, so are other event waits

One factor that I really appreciate about this article is the clear explanation and examples provided for what I usually refer to as cascading resource bottlenecks. The article shows how external CPU pressure can make it difficult for the SQLOS scheduler to schedule processes, and thereby results in increased wait times across all events. In these cases looking at the processor run queue length (this is a great example of when this counter truly is relevant), as well as the increased signal wait times in the [sys].[dm_os_wait_stats] DMV can expose the root issue. In these cases it “takes time to get scheduled on the CPU, and you only hope that your thread is not pushed off of the CPU, preemptively, by a higher priority thread that is ready to run. This is something to be aware of: on very CPU bound systems, sometimes the waits are not what they seem.”

As many SQL Server pros like to point out, generalizations and quickly jumping to a conclusion can be dangerous - and this is a perfect example of how a generalization that’s not supported by the facts of the case can lead to drawing the wrong conclusion. To use a cheesy, but very relevant catch phrase from my youth (a la GI Joe) - Now you know, and knowing is half the battle.

If you’d like a quick rundown on what I think is most interesting in SQL Server 2008, check out my new white paper at:

http://info.quest.com/KKBlog_WorththeWait_WhitePaper_KKline

Enjoy!

-Kevin

There was a thread I saw on dotnet247 that discussed this behavior, but I’m not sure if it’s what you’re looking for since the goal was to insert a NULL value. What do you want to see instead of 1/1/1900 00:00:00.000? You can always talk to your DBA and ask him to create a default on the relevant column so that a different default value is used.

You could use a default of GETDATE() - which could lead to really bizarre behavior if this is a birthdate…
ALTER TABLE [yourtablename]
ADD CONSTRAINT DF_[yourtablename]_[yourcolumnname]
DEFAULT GETDATE() FOR [yourcolumnname]

Or you could use a different date of your choosing
ALTER TABLE [yourtablename]
ADD CONSTRAINT DF_[yourtablename]_[yourcolumnname]
DEFAULT ‘1978-10-20 04:16:00.000′ FOR [yourcolumnname]

When you insert the table default, you’ll have a value other than 1/1/1900 00:00:00.000.

You should definitely read about Report Snapshots in MSDN to ensure you have all the necessary background information on report snapshots before you try to dive too deep. Once you’ve gone through that, you can find instructions on accessing report snapshots and snapshot histories here:

• Finding and Viewing Reports in Report Manager
  • To select a report, search for a report by name or description, or browse report server folders to find the report you want. In Reporting Services, published reports and folders are represented as a hierarchy similar to those of file systems and Web sites. To find the reports that you want to run, you can browse through the folder hierarchy. Not all reports that are visible in the folder hierarchy are accessible. Some reports may prompt you for your user name and password to determine whether you can access the data source for the report.
• How to: View and Configure Report History (Management Studio)
  • Start Management Studio and connect to the report server.
  • In Object Explorer, navigate to a report. Reports are located under Home, or in a folder under Home.
  • Expand the report folder.
  • Expand the History folder to view report history.
  • To view an individual snapshot, right-click the snapshot you want to view, and then click View Snapshot. A Web browser opens and displays the snapshot.

Hope that helps.

You’re getting a Server Not Found error. You should check your code to ensure the server is referenced correctly and that the appropriate port is available.

Microsoft has an MSDN knowledgebase article listing this error that should help you troubleshoot.