SSWUG-TV
Check out this latest edition of SSWUG-TV to hear Field Reporter Interviews from SQL Server MVP Kevin Kline.
Watch the Show
2012 Class Development Survey Responses
The results of our survey for class topics for trainging next year have been compiled. Click here to see a chart of the responses, Many of the topics we included in the survey were inspired from excellent sessions we saw at the most recent PASS Summit and DevConnections conference
Many of the topics represent changes happening here at SSWUG as we expand the scope of our materials beyond SQL Server, and consider other relevant topics for software developement.
As you can see from reviewing the feedback we received, people want to know about the SQL Server tempdb engine, Powershell, XML and database scalability. However, some of the other responses were popular, such as Scrum and Agile Fundamentals, as well as HTML5 and CSS3 for ASP.NET Developers.
Our survey response awards have been determined, and will be notified by December 23rd.
So, what do you think about the membership’s responses to our survey? Why is a class on the tempdb engine the most wanted course? Why is a course on integrating SharePoint with Lync, Enterprise and the cloud the least interesting to the membership? What did we miss on the topic choices that were presented in the survey? Drop me a note with your thoughts by sending an Email to btaylor@sswug.org.
Cheers,
Ben
Cursors – The Good, the Bad, and the Ugly
This is a topic I have never touched on for Microsoft SQL Server because it is a feature that is often used incorrectly, resulting in poor performance or worse. That is the Bad and the Ugly.
In SQL Server, the engine is optimized for set logic. If you can do anything using sets, SQL performance is probably going to be better using sets rather than cursors.
A Cursor is an SQL technique allowing you to retrieve a set of data one row at a time, and perform work on, or based on that row. Think of it as a foreach loop iterating over a collection.
One problem with cursors is locking. When you open a cursor, it maintains a lock on the table(s) supporting the underlying query until the cursor is closed. If your process takes a while to complete, locks may be maintained on the underlying tables for quite some time. A good way to alleviate this problem is to select the data into a temporary table or table variable, and then open your cursor on the temp table instead.
There are some great uses for cursors. I use them to build data marts on large tables, allowing me to keep the data processed small enough to fit into memory. I determine a technique to create pointers for multiple segments within the large table, placing the results into a temp table. Then I open a cursor against a temp table. Finally, I process the large data with the constraints of a single segment. So, instead of running on large query on say 1 million rows, I run many queries processing only 10,000 rows, allowing the whole query to be performed in available memory.
There are other uses for cursors, often found in user defined functions. I have found the use of a sequence table, and the new ranking functions released in SQL Server 2005 to replace most of my needs for cursors.
At the end of the day, I can’t tell you how many times I have optimized a stored procedure by replacing a cursor with set logic. If you are transitioning into SQL from a procedural language using foreach types of iteration, don’t simply start with cursors because they are easy for you to understand. Learn set techniques. Be humble and go read SQL for Smarties.
Reader Comments Re: SSD
Si:
Interesting note on SSDs in databases from yesterday.
Regarding your comment on putting slow changing data on SSD – I would have thought that data that doesn’t change is generally smaller reference data sets, which means it is either more likely to be cached in buffer cache or would be read from disk more quickly (since the reference data set is smaller and generally “thinner”).
Also, if we consider the amount of change a data warehouse undergoes – this is manly inserts in the data tables (and possibly updates on associated tables), together with index changes.
SSD wear is generally measured in the number of times the drive can sustain a full write cycle – normally x number of times per day for x years.
For Enterprise MLC SSD technology, this can be as good as the disk being completely overwritten 10 times a day for 3 years or 3 times a day for 10 years (see this post http://community.fusionio.com/forums/p/195/705.aspx on the Fusion IO forum).
What percentage of a disk’s contents would change in a typical DW scenario? I would guess not more than a few tens of % per day, which would mean that a Fusion IO card or similar Enterprise MLC SSD technology would be adequate for this use case.
And yes, I realise your post was about Solid State DISKS rather than PCIe Adapters…
@Si – Static Data
I would consider static data to be data that doesn’t change. For example, historical data that we wish to remain online. Once that data becomes archival, it is static. However, it could be rather large. What if you had an SSD dedicated to a single month. You could create your partition, defragment it, and then move it to an SSD for rapid retrieval and processing.
I suppose the same could be done for data warehouse OLAP databases using partitioned tables. The newer data could be on hard disk. But the older data, needed only for building cubes, could be maintained on SSD…it won’t change. Think how fast you could build your cubes using something like that. And the life of the SSD would be longer in that you aren’t constantly changing the contents.
Partitioned data makes it possible to extend the life of an SSD and still take advantage of the performance it brings.
Steve:
Degradation of SSD performance over time depends a great deal on the specific device and the wear-leveling algorithm used – they are not all the same. In particular, Windows 7 (and other recent OS updates) added a new Trim command which newer SSDs can use to better deal with garbage collection.
http://en.wikipedia.org/wiki/TRIM
So the performance degradation issues of SSDs primarily affected early-adopters, but is not really going to be an issue in the future.
$$SWYNK$$
Featured Article(s)
Azure ServiceBus: Connectivity at a Cloud Scale
Azure ServiceBus is a key enabling technology to allowing applications to migrate to the cloud.
Featured White Paper(s)
How to Implement an Effective SharePoint Governance Plan
Written by AvePoint
Moving past the "what" and "why" of governance, an even… (read more)