The Microsoft SQL Server support team uses several internally-written utilities to make it easier to work on a typical customer support case. These utilities may also be useful to database developers and system administrators who work with Microsoft SQL Server 2000 and Microsoft SQL Server 2005. The utilities are collectively called the RML Utilities for SQL Server.
With the RML Utilities you can answer questions such as the following:
· Which application, database or login is consuming the most resources, and which queries are responsible for that.
· Whether there were any plan changes for a batch during the time when the trace was captured and how each of those plans performed.
· What queries are running slower in today's data as compared to a previous set of data.
PSS SQL Server Engineers : RML Utilities for Microsoft SQL Server Released
Updating a large table is a common scenario. This is often encountered in cases of applications that performs series of tasks in the background or data warehousing loading scenarios. To reduce locking and logging resources, such update statements are broken down into smaller batches or units of work. This has been traditionally done using SET ROWCOUNT setting in SQL Server. Now, SQL Server 2005 provides you with a simpler construct that the optimizer can understand and use efficiently. TOP clause has been enhanced in SQL Server 2005 to support expressions and can be used now in all DML operations. Let's look at a quick example on how to use TOP with UPDATE:
I think sp_msforeachdb might be a solution that's a bit easier to read, but this works too.
Get the top 10 statements that took the most time for each database on a SQL 2005 server.
select coalesce(QUOTENAME(DB_NAME(t.dbid), '"'), '') /* NULL means resource db*/
+ N'.'
+ QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.dbid), '"')
+ N'.'
+ QUOTENAME(OBJECT_NAME(t.objectid, t.dbid), '"') as full_obj_name
, SUBSTRING(t.text, (t.statement_start_offset/2)+1,
((CASE t.statement_end_offset
WHEN -1 THEN DATALENGTH(t.text)
ELSE t.statement_end_offset
END - t.statement_start_offset)/2) + 1) AS statement_text
, t.*
from (
select *, DENSE_RANK() OVER(PARTITION BY t.dbid ORDER BY qs.total_elapsed_time) as rnk
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as t
where t.objectid is not null
) as t
where t.rnk <= 10
order by t.dbid, t.rnk;
Effectively, this will periodically determine top index candidates for your workload. It currently runs in a recommendation mode, but you can also have it run fully automated if you uncomment a line in the file.
I don't like cursors. Usually there are ways to avoid them, more so in SQL 2005. But if they exist in your environment and are causing performance problems, take a look at this article.
If you have to optimize a cursor for performance, keep the following considerations in mind:
- Always try to replace the cursor by a set-based equivalent first. If you fail to see how, do not hesitate to ask in one of the SQL Server newsgroups.
- If you are really stuck with a cursor, then do NOT rely on the default options. They will result in the slowest of all possible option combinations
- If you think that the FAST_FORWARD option results in the fastest possible performance, think again. I have not found one single test case where it was faster than, or even as fast as, a STATIC cursor.
- Do NOT use the WHERE CURRENT OF syntax of the UPDATE command. Using a regular WHERE clause with the primary key values will speed up your performance by a factor of two to three.
- Do not rely blindly on my performance results. Remember, the one thing that is always true when working with SQL Server is: “it depends”.
Scans vs. Seeks
Scans and seeks are the iterators that SQL Server uses to read data from tables and indexes. These iterators are among the most fundamental ones that we support. They appear in nearly every query plan.
/// <summary>
/// Web Utility Function For Exporting Data Set to Specified Format
/// </summary>
/// <param name="dsResults">Result Data Set</param>
/// <param name="enExport">Export Enum Values</param>
/// <param name="strColDelim">Column Delimiter value</param>
/// <param name="strRowDelim"></param>
/// <param name="strFileName"></param>
public static void ExportDataSet(DataSet dsResults , ExportFormat enExport,string strColDelim, string strRowDelim, string strFileName)
{
DataGrid dgExport = new DataGrid();
dgExport.AllowPaging =false;
dgExport.DataSource =dsResults;
dsResults.DataSetName ="WebERP";
dgExport.DataMember = dsResults.Tables[0].TableName;
dgExport.DataBind();
System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.Buffer= true;
System.Web.HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
System.Web.HttpContext.Current.Response.Charset = "";
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" +strFileName );
switch(enExport.ToString().ToLower())
{
case "xls":
{
System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
dgExport.RenderControl(oHtmlTextWriter);
System.Web.HttpContext.Current.Response.Write(oStringWriter.ToString());
break;
}
case "custom":
{
string strText;
System.Web.HttpContext.Current.Response.ContentType = "text/txt";
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
dgExport.RenderControl(oHtmlTextWriter);
strText = oStringWriter.ToString();
strText = ParseToDelim(strText ,strRowDelim,strColDelim);
System.Web.HttpContext.Current.Response.Write(strText);
break;
}
case "csv":
{
string strText;
strRowDelim = System.Environment.NewLine;
strColDelim = ",";
System.Web.HttpContext.Current.Response.ContentType = "text/txt";
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
dgExport.RenderControl(oHtmlTextWriter);
strText = oStringWriter.ToString();
strText = ParseToDelim(strText ,strRowDelim,strColDelim);
System.Web.HttpContext.Current.Response.Write(strText);
break;
}
case "tsv":
{
string strText;
strRowDelim = System.Environment.NewLine;
strColDelim = "\t";
System.Web.HttpContext.Current.Response.ContentType = "text/txt";
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
dgExport.RenderControl(oHtmlTextWriter);
strText = oStringWriter.ToString();
strText = ParseToDelim(strText ,strRowDelim,strColDelim);
System.Web.HttpContext.Current.Response.Write(strText);
break;
}
case "xml":
{
System.Web.HttpContext.Current.Response.ContentType = "text/xml";
System.Web.HttpContext.Current.Response.Write(dsResults.GetXml());
break;
}
case "htm":
{
System.Web.HttpContext.Current.Response.ContentType = "text/html";
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
dgExport.RenderControl(oHtmlTextWriter);
System.Web.HttpContext.Current.Response.Write(oStringWriter.ToString());
break;
}
}
}
}
#region "Export To a Delim Format"
public static string ParseToDelim(string strText, string strRowDelim , string strColDelim)
{
Regex objReg = new Regex(@"(>\s+<)",RegexOptions.IgnoreCase);
strText = objReg.Replace(strText,"><");
strText = strText.Replace(System.Environment.NewLine,"");
strText = strText.Replace("</td></tr><tr><td>",strRowDelim);
strText = strText.Replace("</td><td>",strColDelim);
objReg = new Regex(@"<[^>]*>",RegexOptions.IgnoreCase);
strText = objReg.Replace(strText,"");
strText = System.Web.HttpUtility.HtmlDecode(strText);
return strText;
}
#endregion
public enum ExportFormat
{
XML,
XLS,
HTML,
CSV,
CUSTOM,
TSV
}
/* Robyn and Phil return with some fresh ideas about how to import text files into SQL Server, without resorting to DTS or SSIS scripting. They go on to show how much can be done in TSQL */
htmlSQL is a experimental PHP class which allows you to access HTML values by an SQL like syntax.
This means that you don't have to write complex functions (regular expressions) to extract specific values.
Sounds like Beijing Ticketmaster ran out of log space in their database... damn holes in the optimization processes. I hate those things... Should have put in some failover systems or something. though that probably wouldn't have kept up with the onslaught in demand.
Within the first hour, from 9:00am to 10:00am, the page view (PV) volume of the official ticketing website of the Beijing 2008 Olympic Games (http://tickets.beijing2008.com) soared to eight million, while the number of calls to the ticketing hotline (+8610 952008) exceeded 3.8 million.
That's a lot of hits.
A ticketing official later said that after Phase 2 of the ticket sales began, an excessive volume of page views to the official ticketing website affected the database performance, causing in a "hole" in the optimization of the database processes and slowing the online search speed. In addition, new ticket buyers were unable to register with the system as a result.
After the second phase of the domestic ticket sales was launched on October 30, the public's purchase of tickets surpassed the system processing capability by more than eight times, leading to a "paralysis" of the official Olympic ticketing website. As a result, BOCOG decided to temporarily suspend the Phase 2 domestic ticket sales and issued an explanation for the situation on October 31.
On November 5, BOCOG released a new ticketing policy based on a comprehensive analysis of the situation and system capability.
The new ticketing policy for the Phase 2 domestic ticket sales institutes a unified ticket lottery system, whereby the order of ticket application submission will not matter. The ticket purchase limit will be adjusted accordingly, and the public may submit ticket applications from December 10 through December 30.
According to reports, the BOCOG Ticketing Center immediately began to work with the BOCOG Technology and Security departments and Beijing Gehua Ticketmaster Ticketing Co., Ltd to adopt measures to solve the technical problems as soon as they arose.
More here. http://blogs.techrepublic.com.com/tech-news/index.php?cat=931
Looks like the SQL 2008 price is staying the same as 2005. Interesting to note that many customers have not even upgraded to SQL 2005 yet. Launching 2008 should speed adoption of 2005. Customers always seem to want to stay 1-2 versions behind because of the appearance of stability & being a "proven" architecture. In my opinion there is no reason not to ditch SQL 2000 for SQL 2005, if you have any need whatsoever for better reporting and performance capabililties.
Source: PC World - No Price Hike for SQL Server
Using 64-bit architecture and the increased memory capacities of SQL 2005 Enterprise should net some serious performance improvements for those who upgrade. Not to mention the management views that will make analyzing and boosting performance in systems much easier.
Hopefully they launch the first service pack a month after, so that the early-adopter crowd will go all in on an upgrade.
One feature being hyped is "Pervasive Insight". This appears to be the external auditing capabilities that wrap around the database. There also appears to be some integration of the BIDS add-in into the base platform, to troubleshoot issues with dimension relationships and warnings in Analysis Services.
More info below.
http://blogs.msdn.com/ajaiman/archive/2007/06/05/sql-server-2008-june-ctp.aspx
Huge list of resources at this site.
OLAP/Data Mining
Online Analytical Processing (OLAP)
What Is OLAP
http://www.cis.upenn.edu/~cse330/lecture14/sld002.htmOLAP Council
The OLAP Report
OLAP Journal
Data Warehousing and OLAP Research Bibliography
http://www.cs.toronto.edu/~mendel/dwbib.htmlOLAP Information Site
Visual OLAP Resources
OLE DB For OLAP
http://www.microsoft.com/data/oledb/olap/default.htmMicrosoft OLAP Services User Community
http://groups.msn.com/MicrosoftOLAPServicesUsersCommunity/_homepage.msnw?pgmarket=en-usMicrosoft OLAP
Microsoft SQL Server OLAP Services
http://msdn.microsoft.com/vfoxpro/technical/olap.aspMicrosoft OLAP Unleashed (Book) 1999
http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?srefer=&isbn=0672316714Microsoft OLAP Solutions Updates
http://www.dsslab.com/MSOLAPSolutions.htmlFIX: Processing OLAP Partitions in Parallel May Result in Errors (Q253496)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q253496SAS Business Intelligence & OLAP
http://www.sas.com/technologies/olap/SAS Analytic Intelligence
http://www.sas.com/technologies/data_mining/OLAP and OLAP Server Definitions
http://www.moulton.com/olap/olap.glossary.htmlThe OLAP Survey
http://www.survey.com/reports/olap/Active OLAP For Essbase
EASY OLAP
http://www.peyo-home.sk/index.html?easyolap/index.htm~mainOLAP Technical Work Forum
http://www.tek-tips.com/gthreadminder.cfm/lev2/77/lev3/25/pid/450German OLAP and Data Warehouse Forum
http://olap.winf.ruhr-uni-bochum.de/OLAP Forum
http://www.ontargettek.com/wwwboard/wwwboard.htmlOLAP Train
OLAP Links
http://www.webopedia.com/TERM/O/OLAP.htmlOLAP Resources
http://www.sgroves.demon.co.uk/olaplnks.htmOpen Directory: OLAP
http://dmoz.org/Computers/Software/Databases/OLAP/Google Web Directory: OLAP
http://directory.google.com/Top/Computers/Software/Databases/OLAP/Databases, Data Warehousing and OLAP
http://www.kdnuggets.com/websites/dbolap.htmlIBM Welds OLAP, Data Mining
http://www.idg.net/ic_855480_8434_1-3921.htmlIBM DB2 OLAP Server
http://www-3.ibm.com/software/data/db2/db2olap/IBM Adds Hybrid OLAP In DB2
http://www.eweek.com/article2/0,3959,120907,00.aspOracle OLAP
http://otn.oracle.com/products/oracle9i/datasheets/olap.pdfOracle9i OLAP
http://www.oracle.com/ip/deploy/database/oracle9i/index.html?bi_olap.htmlOracle Express OLAP
http://www.charlesriver.com/titles/oracle.htmlOLAP Information
http://www.andypryke.com/university/dm_docs/olap/A New Face For OLAP
http://www.dbmsmag.com/9701i08.htmlWhite Papers On Data Warehousing, Data Mining, OLAP
http://www.dkms.com/white_papers.htmJSR 69 Java OLAP Interface (JOLAP)
http://www.jcp.org/jsr/detail/69.jspOLAP/Multidimensional Database Tools
http://www.dwinfocenter.org/olap.htmlOLAP Performance
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=7638The Transition of OLAP
http://www.101com.com/solutions/dataintelligence/article.asp?articleid=4650Intelligent Rollups In Multidimensional OLAP Data
http://www.it.iitb.ernet.in/~sunita/papers/vldb01.pdfMaintenance and Control Aspects of OLAP Applications
http://www.xs4all.nl/~fab/olapkeep.htmlOLAP and Statistical Databases: Similarities and Differences
http://citeseer.nj.nec.com/shoshani97olap.htmlMining/OLAP
http://www.cio.com/research/data/data_mining.htmlDiscovering New Opportunities With OLAP
http://webevents.broadcast.com/ibm/olap/home.aspOLAP Tools
http://www.meridian-marketing.com/TECH/olap.htmlOLAP Market Review
http://www.dmreview.com/master.cfm?NavID=198&EdID=4085Evaluating OLAP Servers and Tools
http://www.corpinfodesigns.com/priv_docs/EVAL_OLAP.pdfOLAP For Java Developers
http://www.devx.com/upload/free/features/javapro/1999/07jul99/kn0799/kn0799.aspMining For Golden Nuggets of Data
http://www.toptentechs.com/applications/Issue6/A Survey On Logical Models For OLAP Databases
http://www.dbnet.ece.ntua.gr/~dwq/p31.pdfSpecifying OLAP Cubes On XML Data
http://www.cs.auc.dk/~tbp/articles/R015003.pdfWhat Is Data Mining ?
http://www.anderson.ucla.edu/faculty/jason.frand/teacher/technologies/palace/datamining.htmData Mining Overview
http://www.megaputer.com/dm/index.php3Defining Data Mining
http://www.dbmsmag.com/9608d53.htmlData Mining and Knowledge Discovery Journal
http://www.digimine.com/usama/datamine/Information About Data Mining
IBM Data Mining Group Quest
http://www.almaden.ibm.com/cs/quest/IBM DB2 Intelligent Miner For Data
http://www-3.ibm.com/software/data/iminer/fordata/Data Mining, Web Mining and Knowledge Discovery Guide
National Center For Data Mining (NCDM)
Data Mining News
Data Mining Books, White Papers, and Tutorial
Data Miners
Data Mining and Machine Learning
http://www.cs.helsinki.fi/research/fdk/datamining/Effectively Guide Your Organization's Future With Data Mining
http://www.spss.com/spssbi/applications/datamining/Data Mining Benchmarking Association
http://www.dmbenchmarking.com/URLs For Data Mining
http://www.galaxy.gmu.edu/stats/syllabi/DMLIST.htmlData Mining In Molecular Biology
http://industry.ebi.ac.uk/~brazma/dm.htmlData Mining Glossary
http://www.twocrows.com/glossary.htmData Mining Technologies
DMSK: Data Miner Software Kit
ACM Special Interest Group On Knowledge Discovery in Data and Data Mining (SIGKDD)
The Data Mining Institute (DMI)
Data Mining and Knowledge Discovery in Databases
http://db.cs.sfu.ca/sections/publication/kdd/kdd.htmlUntangling Text Data Mining
http://www.sims.berkeley.edu/~hearst/papers/acl99/acl99-tdm.htmlData Mining Forum
http://www.data-mining-forum.de/Data Warehouse, Data Mining and Decision Support Resources
http://www.infogoal.com/dmc/dmcdwh.htmData Mining Lecture Notes
http://www-db.stanford.edu/~ullman/mining/mining.htmlData Mining: Concepts and Techniques (Book) 2000
http://www.cs.sfu.ca/~han/DM_Book.htmlAnalysis of Data Mining Algorithms
http://userpages.umbc.edu/~kjoshi1/data-mine/proj_rpt.htmTutorial On High Performance Data Mining
http://www-users.cs.umn.edu/~mjoshi/hpdmtut/ITSC Data Mining Center
http://datamining.itsc.uah.edu/Data Mining Techniques
http://www.statsoftinc.com/textbook/stdatmin.htmlAdvances In Knowledge Discovery and Data Mining (Book)
http://www.aaai.org/Press/Books/Fayyad/fayyad.htmlData Mining Tools
http://www.dwinfocenter.org/datamine.htmlData Mining: Practical Machine Learning Tools and Techniques with Java Implementations (Book) 1999
http://www.cs.waikato.ac.nz/~ml/weka/book.htmlData Mining With Predictive Models
http://www.spss.com/spssbi/clementine/Open Directory: Data Mining
http://dmoz.org/Computers/Software/Databases/Data_Mining/Data Mining Resources For Space Science
http://adc.gsfc.nasa.gov/adc/adc_datamining.htmlData Mining Group
http://cslab.anu.edu.au/ml/dm/NAG Data Mining Components To Create Critical Competitive Advantage
http://www.nag.co.uk/numeric/DR/drdescription.aspData Mining Approaches for Intrusion Detection
http://www.cs.columbia.edu/~wenke/papers/usenix/usenix.htmlData Mining in Python
http://ai.fri.uni-lj.si/~aleks/orng/Data Mining Product Features
http://www.exclusiveore.com/prodtable.htmlKnowledge Discovery/Data Mining Center
http://open.cineca.it/datamining/index_ing.htmUCLA Data Mining Lab
Data Mining Solutions and Establishment of A Data Warehouse
http://www.firstmonday.dk/issues/issue2_5/maxwell/Data Mining - Case Study
http://www.math.mcmaster.ca/peter/sora/case_studies_00/data_mining.htmlWorkshop On Data Mining Lessons Learned
http://www.hpl.hp.com/personal/Tom_Fawcett/DMLL-workshop.htmlData Mining and Customer Relationships
http://www3.primushost.com/~kht/text/whexcerpt/whexcerpt.htmOMG Common Warehouse Metamodel (CWM)
Data Warehousing, CWM and MOF Resources
