SQL Server Express vs SQL Server performance [closed]?

Before we talk about how storage works, we need to understand what SQL Server needs. And cover their IO patterns and trends. For each, we’ll then go into detail about how certain kinds of activities change those requirements requirements and how to monitor resource usage using DMVs.

Now that we understand what SQL Server needs from our storage, we can explore the different kinds of storage available to us. Shared storage makes things a lot more complicated, but we’ll demystify the black box. And show how to get the right configurations from your SAN administrators.

SQLOS helps SQL Server perform by managing queries, internal jobs, the buffer pool, and many other shared resources. The underlying hardware, and how you may need to tweak it in order to really push the scaling limits. The Waits and Queues performance tuning methodology is the cornerstone of investigating performance issues with SQL Server.

This module will cover the methodology, common wait patterns and what they mean, as well as some deeper investigative techniques. Once you’ve identified a query or batch that is associated with a performance issue, you’ll want to look at the query execution plan and see if there are ways that you can address the problem (or problems). First step for a CPU issue – is it SQL or not?

Okay it’s SQL – but is it Kernel or User time? Triage the prime suspects (e.g. SQL Server contains a plethora of DMVs for gaining insight into what's happening (and happened) on the system. DMVs and show you their practical application.

This module will cover the various patterns to watch for when troubleshooting memory-related bottlenecks. One of the fundamentals of performance tuning is being able to tell how performance has changed from a known point, and also what your system is capable of. Techniques for load testing and creating a baseline.

Traces through jobs, Transact-SQL and Performance Monitor counter thresholds as well as collecting the “RIGHT” data for analysis. In addition to benchmarking and baselining, it is also important to know how to collect performance data on an ongoing basis. SQL Server 2008 offers different options for collecting this data, which we’ll discuss in this module.

Important is knowing how to use the data you collect it and analyze it. Therefore we’ll also spend some time looking at some of the secondary tools that can be used to analyze collected data.