Tag Archives: Microsoft SQL Server

FunkyKB: “Explicit vs implicit SQL joins” and other notes…

Posted on by
Reply

Stack Overflow: “Personally I prefer the join syntax as its makes it clearer that the tables are joined and how they are joined. Try compare larger SQL queries where you selecting from 8 different tables and you have lots of filtering in the where. By using join syntax you separate out the parts where the tables are joined, to the part where you are filtering the rows.”

“How do i write the literal "]]>" inside a CDATA section with it ending the section?”

Stack Overflow: “This ends up breaking the CDATA section in two parts, but it’s what you have to do…” 

]]&gt;]]&gt;<![CDATA[

Issues like this should be more and more moot/antiquated for my needs as the .NET framework in general and LINQ to XML in particular provide facilities that make these “tricks” unnecessary.

More Old Stuff: “What’s New in FOR XML in Microsoft SQL Server 2005”

MSDN: “There will still be a few cases where you may want to use the EXPLICIT mode (in order to generate CDATA sections or to use the !xmltext directive, for example), but the new functionality should make the “query from hell” a much less frequent encounter.”

“SQL Server Index Basics”

simple-talk.com: “For composite indexes, take into consideration the order of the columns in the index definition. Columns that will be used in comparison expressions in the WHERE clause (such as WHERE FirstName = 'Charlie') should be listed first. Subsequent columns should be listed based on the uniqueness of their values, with the most unique listed first. ”

Notes on Yavor Angelov, His PDC 2009 Presentation, “Making Microsoft SQL Server 2008 Fly”

Posted on by
Reply

the green girl had oatmeal at jamba juice for breakfast this morning

Microsoft’s Yavor Angelov PDC 2009 presentation, “Making Microsoft SQL Server 2008 Fly,” brought me to these points:

  • The Database Engine Tuning Advisor—it seems to come with SQL Server 2008.
  • “The missing indexes feature uses dynamic management objects and Showplan to provide information about missing indexes that could enhance SQL Server query performance.” Missing indexes is on by default.
  • “What is ‘parameter sniffing’? When a stored procedure is first executed SQL Server looks at the input parameters and uses this as guidance to build the query plan. This is known as ‘parameter sniffing.’”
  • “Parameter use, especially in more complex scenarios, can also cause performance issues. If the queries are complex and/or the data distribution on the columns against which the parameter is compared vary, the cost of different plan choices can change. A plan that is optimal for one parameter value may perform poorly for another value. The query optimizer still needs to estimate the selectivity and cardinality of predicates using a parameter value. This section describes how this process works in more detail.”
  • “Static” parameters for stored procedures yield better execution plans than do local variables or mutable parameters.
  • “SQL Server offers many hints that can be used to force how an execution plan is put together. The option that we are interested in is the OPTIMIZE FOR option. This will allow us to specify what parameter value we want SQL Server to use when creating the execution plan. This is a SQL Server 2005 hint.”—Greg Robidoux
  • “Queries that can benefit from plan guides are generally parameter-based, and may be performing poorly because they use cached query plans whose parameter values do not represent a worst-case or most representative scenario. The OPTIMIZE FOR and RECOMPILE query hints can be used to address this problem. OPTIMIZE FOR instructs SQL Server to use a particular value for a parameter when the query is optimized. RECOMPILE instructs the server to discard a query plan after execution, forcing the query optimizer to recompile a new query plan the next time that the same query is executed.”
  • “In the previous example, the value for the @stmt parameter is the parameterized form of the query. The only reliable way to obtain this value for use in sp_create_plan_guide is to use the sp_get_query_template system stored procedure. The following script can be used both to obtain the parameterized query and then create a plan guide on it.”
  • “SQL Server 2008 supports both row and page compression for both tables and indexes. … Compression is available only in the SQL Server 2008 Enterprise and Developer editions.”

Working for Songhay System ‘Ready State’

Posted on by
2

I am not a military scientist but I seem to insist that this concept I call ‘ready state’ comes from the military. ‘Ready state’ means that you have worked with your organization and have achieved a certain level of preparation. This level of preparation is ready to handle a certain set of known scenarios. For me this implies that there are (at least) two kinds of work: you work to obtain a certain level of preparation and you work to maintain a certain level of preparation.

I am not a Tibetan monk but I seem to know about this way of suffering called “the suffering of change.” This implies that whenever I use words of permanence like “maintain” I am making myself vulnerable to the suffering of change. Life is about constant change—and any “intelligent grasping” for the illusion of permanence is a recipe for the Blues, baby.

My clever grasping would suggest to any willing to listen that my two kinds of work are worth it. And, in fact, obtaining a realistic ‘ready state’ is a defense against the suffering of change. The delicate, professional, career-orienting move here is to prepare for these ‘known scenarios’ but also be ready to abandon them completely. So, with my Songhay System organization, I have been ‘suffering’ for years working toward reaching a level of preparation in these areas:

  • Building a generic solution (now called ‘GenericWeb’) that generalizes the document-centric Web application. This is largely server-side work in ASP.NET, XSLT and Microsoft SQL Server with a little bit of client-side AJAX/CSS under YUI.
  • Building a generic solution (with no fancy name—so let’s call it ‘Next-Generation Songhay UI’) for displaying content on the Web. This is largely a client-side effort, using AJAX/CSS under YUI, with a little bit of server-side Zend Framework/PHP/XSLT.

These two work areas described with the buzzwords above suggest the following:

  • The Songhay System is using XSLT to render user interfaces (with AJAX). This implies that XML must be used to represent data that “bound” to these interfaces. This further implies that an XSLT/XML “pipeline” had to be built in PHP and .NET.
  • The PHP-based solution described above mentions no database systems. This does not mean that databases (like SQLite) are not being used. What this means to imply is that something other than direct contact with a DBMS is the future here. Yes, we can speak of “cloud computing” but for the humble scale of the Songhay System we can look at a sample of how this ‘next-generation’ UI can connect to a WordPress Blog and pull data from an RSS feed.

Why prepare so much for some theoretical scenarios when you may have to abandon them completely? Well… why be born when you know you are going to die? The essentials of what I am grasping for here in this IT context are these:

  • XML is the preferred way of transporting data across tiers. This preference for XML influences the desire for user interface technologies that support XML-based, declarative, techniques (e.g. XAML, E4X in Flex, XHTML and HTML 5).
  • XML is the preferred way of transporting data across tiers. This preference for XML influences the desire for data management technologies that support XML-based, techniques (e.g. SQL Server 2005 and above).
  • The use of XSLT and certain “good parts” of JavaScript directs my work toward functional programming for purely pragmatic reasons.

So what’s ‘ready’? I think I am ‘close’ to ready-state nirvana. More journal entries to come… Here are some proposed milestones for this journey toward ‘ready state’:

  • An upgrade to my SonghaySystem.com web site will use this ‘next-generation’ UI. This release would be a strong indicator of readiness.
  • New .NET projects from me appearing in CodePlex.com or in the “MSDN Code Gallery” would be a strong indicator of readiness.
  • New, formal documentation for all the mess I’m talking here showing up at SonghaySystem.com is definite readiness.

These would-be achievements do not represent something I would impose upon you in order to justify its existence. These achievements represent my personal technology strategy—my proposed expression of sanity amidst crazy worlds of proprietary technologies. It is one thing to whine and complain about another strategy (supposedly outside of one’s “self”)—it is another matter (according to my illusions) to “possess” what represents a technology strategy that can be considered ‘ready.’ I even I am not concerned about you using “my” solution. My concern is that a solution—that is actually regarded by me as a solution—exists.

What I find, after almost twenty years in IT, is that I have solutions to problems that many don’t even regard as real. This is one of two reasons why my stackoverflow.com score is so low!

SharePoint Services and SQL Reporting Services Must Start Manually on My Server

Posted on by
Reply

Buy this Book at Amazon.com! Here among the cubicles, a consultant from Microsoft told us to install SQL Server 2005 with Microsoft Office SharePoint Services. Since my development VM disk suffered hard disk sector and index problems for the first time, I am thinking of reasons why he told us to stay away from SQL Server 2008.

The Application Log of my VM was defiled by actual errors! It was SharePoint trying to log into my SQL Server 2008 instance but it wasn’t ‘ready’ to accept connections… I strongly suspect that SharePoint services will try to access the database during system startup and shutdown—when services SPTimerV3, SPTrace, SPAdmin and SPSearch are set to start automatically. What’s a dangerous possibility is that SharePoint might try to write to the database during a system shutdown. The 2008 SQL instance might ‘abandon’ SharePoint, leaving it to perhaps damage the disk with its unmanaged, wild-ass DCOM parts.

So it turns out that I have already written a PowerShell script to shut down services so I can run SyncToy 2.0 against things like *.mdf files. I run these backups from my VM before shutdown. Now with SharePoint and SQL Server 2008, I’m going to have to run this script after startup—to kick off the SharePoint services (SPTimerV3, SPTrace, SPAdmin and SPSearch) I have set to “manual.”

It’s been a tough week.

Random Screenshot: SQL Server Express Hitting the 4GB Limit

Posted on by
Reply

SQL Server Express Hitting the 4GB Limit

This one is a first for me. This error is in the form of:

Could not allocate space for object 'TableName' in database 'DatabaseName' because the 'PRIMARY' filegroup is full.

I don’t really expect to work with databases this huge—well within tens of millions of rows. But I thought it would be cute to import and analyze the IIS server logs of a couple of our W2-labor-camp machines and got in over my head.

  • First, there are two sets of logs on two boxes (behind one load balancer).
  • Second, these two different sets of logs are in two different IIS log formats!
  • Third for the three-month sample to analyze there are upwards of 20 gigs of text files to record.

So I had to whip out the MSDN Universal Subscription and upgrade to SQL Server 2008 Developer Edition. It has been three days and I’m still loading records!