I wish the search on the downloads site at Microsoft worked better. You can even input the name or title of this one and still no luck.....
You can download "Troubleshooting Performance Problems in SQL Server 2008" from the Microsoft Download Center.
It is a 63 (a few less once you subtract table of contents, etc) page document packed with a lot of good information. For those who are looking for a troubleshooting methodology, this document covers the basics and how to get started.
It also covers using three of the new DMVs added in SQL 2008 and how to use them:
- sys.dm_os_memory_brokers
- sys.dm_os_memory_nodes
- sys.dm_exec_procedure_stats
Tags: sql 2008,
performance,
troubleshooting
Categories: SQL Server |
Training and Education
I'd almost mark this to a new category titled: "Trivia".
At least, that's what it feels like – particularly when it came to finding out how to do this.
Scenario: Client has a financial package running on an AS/400, IBM OS v5 r4, which has DB2 for the iSeries installed. We are building a lightweight application to do some gap filling on this package so some basic reporting can be done while they wait to move to the new one. New data will be stored in SQL Server, however, necessary key descriptions and financial data points live in the AS 400 – and no one on the staff wants to maintain an SSIS package to pull over just a little bit of data.
So what to do? Connect directly to the DB2 database via .Net and surface up the few necessary data points in the application. Sounds simple, right?
Ah – the other bit in the scenario - -we need to do this using .Net OleDB versus the IBM native .Net driver so we don't have to configure the production web servers (also being upgraded soon).
Still sounds like it should be simple though – and connecting is.
And that is where you will probably come across text and other variable character data that comes over as binary. This is where the fun begins..
DB2 has some interesting data types for storing text data (reference this link for supported sql data types for the DB2 .Net data provider), and when you run across these in work with DB2, it can be frustrating trying to translate the binary that is coming over into usable text.
One of the keys to resolving the mystery is figuring out what code page the IBM machine is using – in many cases this can be a little known (now a days) and IBM only code page lovingly titled "Extended Binary Coded Decimal Interchange Code" or EBCDIC. In using C# to decode the binary into text, this is a crucial bit of information.
In this instance, I am using EntLib, and the connection is pretty much the same as any DBMS. Below is the pertinent code that decodes the record:
using (IDataReader reader = db.ExecuteReader(System.Data.CommandType.Text, commandtext))
{
while (reader.Read())
{
byte[] b = (byte[])reader[0];
//here is where we tell the Encoder what code page we need
Decoder ebcdicDecoder = Encoding.GetEncoding(37).GetDecoder();
//initialize a char array that will hold the decoded output
char[] c = new char[ebcdicDecoder.GetCharCount(b, 0, b.Length)];
//call into the decoder and get the decoded char array
ebcdicDecoder.GetChars(b,0,b.Length,c,0);
//convert to a string if necessary
string accountCodeId = new string(c);
//do whatever else you want to do with the results now
}
}
When I was first getting the byte array back, a coworker and I were puzzled at the values in the byte array, as they did not match up to most U.S. character sets we would have expected to see—then we stumbled across Wikipedia's entry for IBM code pages (and EBCDIC in particular) and the pieces started to come together. We were able to look at the integer values coming across in the byte array and see in the code table what the text values were – and that intelligible values were in those arrays.
The next bit was finding what code page on the Microsoft side to use to decode the byte array – which did not turn out to be as easy as I expected, but in the end, I stumbled across this MSDN entry on Host Integration Server 2000 and EBCDIC code page support. With this last bit (the 37 you see in the code on Encoding.GetEncoding(37), I was finally able to get the records translated.
Hopefully this will save some other time, headache and pain pill abuse – please feel free to e-mail me if you have any questions about this or leave a comment!
Tags: ibm,
db2,
code page.net,
c#
Categories: .NET
One nice resource for training, and recently discovered in my case, is JumpstartTV, a division of Fourdeuce, Inc, which in turn is owned by End To End Training, a provider of technical training. Fourdeuce has some recognizable owners in the SQL Server world: Andy Warren, Brian Knight and Steve Jones (if you don't know them, their blogs are behind the links on their names, get to know them, they are pretty darn good).
Currently, they have three "channels": SQL, Misc and .Net. I think if you made the guess that JumpstartTV is pretty loaded on the SQL channel end, you would be right, give that it was started by three SQL aficionados, it only makes sense!
They have posted some pretty good topics out there, and similar to most blog posts, videos are sorted by tags and ranked by popularity. JumpstartTV is open for anyone to become a paid author, with many individuals having contributed already.
If you do not have this as a training resource already, I highly recommend it – videos are at most five minutes, so it is a great way to take a look at a new topic!
Tags: training,
sql 2008,
ssis,
.net,
security,
sql server,
education
Categories: Training and Education
Join the Central Texas BI User Group on LinkedIn group to get announcements, start a discussion, share an article, etc.
Here is the URL for the group: http://www.linkedin.com/groups?gid=1912918.
Tags:
Categories: Business Intelligence
For anyone unfamiliar with the TPC (Transaction Processing Performance Council), they are an independent non-profit with the goal of creating benchmarks for database systems that are verifiable and reportable to the industry.
It is worth taking a look at their site and some of the research; if nothing else, you may get some ideas on reference hardware for your next implementation or use their posted data sets for benchmarking your own solution's performance (Microsoft did this when declaring themselves the holder of the ETL world record).
In this latest announcement, the TPC reiterated that they are developing an ETL standard; when this is finally published, it will be interesting to see how all of the individual platform vendors respond, most notably for me being the battle between SSIS and Informatica – a done deal in my mind. Informatica has a great toolset, but extensibility, usability, cost and speed in development and training all suffer greatly in my mind when compared to SSIS.
Other benchmarks: http://www.tpc.org/information/benchmarks.asp
Tags: tpc,
ssis,
performance,
statistics,
hardware,
industry standards
Categories: SSIS
Catapult Systems will be working with Microsoft to sponsor the first Central Texas Business Intelligence User Group.
The Central Texas BI User Group's objectives are to:
- Build community around BI and related technologies
- Cross-pollinate information and ideas on BI
- Provide a forum for companies to share innovative programs and solutions based on and related to BI
Dates:
- Wednesday, June 17th
- Thursday, August 13th
- Wednesday, October 21st
Format:
- Welcome Time: 5:30-6:15
- Intro & Logistics: 6:15 - 6:30
- Panel Q&A: 6:30-7:00
- Presentation: 7:00-8:00
Location:
Microsoft Office
9606 N. Mopac Expressway Suite 200
Austin, TX 78759
I will be presenting the first topic on SSRS 2008 integration with MOSS. I normally would have considered this topic somewhat stale – SSRS 2005 and MOSS 2007 integration topics have been out for a while, however this topic was chosen based on interest by our clients and the lack of true production implementations. This presentation will show an implementation on a small farm (no single server and thus not real world example!) with practices that you can immediately take away.
Please feel free to e-mail me at sid.atkinson "at" catapultsystems.com to get on the distribution list for this and future events.
Tags: bi user group,
business intelligence
Categories: Business Intelligence
Unfortunately, Microsoft has cancelled this year's BI conference in Seattle, WA. More on the reasons at the official Microsoft BI Blog.
Microsoft will cover BI technology and features at other conferences this year such as TechEd, the World Wide Partner conference, the SharePoint conference and SQL Server PASS.
Currently, the Microsoft BI conference will be a biannual event, making the next event October 2010.
Tags: bi conference,
business intelligence,
conferences
Categories: Business Intelligence
I made a post over a week ago talking about extending the Data Profiling task and in there I just cover the some of the basics on my methods for extending it and what goals I had in mind.
It actually sparked a few e-mails and so, due to interest, I am going to graduate my sloppy proof-of-concept code into a workable project. To put further rigor and transparency, I am going to put this project on CodePlex so others can contribute and enjoy.
Anyone interested in helping in the development, feel free to shoot me an e-mail. Per CodePlex rules, I've got 30 days to post code and publish to the project, so the clock is ticking!
My goal is that this will be a simple app where you plug in a connection string to a SQL Server and your output is a document with the profile results.
For further reading on this topic, John Welch, a Microsoft MVP, was kind enough to leave a comment about my prior post as he had the same issue with the Data Profiling task as I had. For anyone wanting to look at his work around to make the profiling task more flexible, please reference his post , "Using the Data Profiling Task to Profile All the Tables in a Database", here.
Tags: ssis,
data profiling,
sql 2008,
codeplex
Categories: SSIS
Before anyone jumps off a bridge, it is not as bad as the headline sounds; stay calm, step down from the ledge and we can work this out.
Background: I got tired of running PerformancePoint in a VPC all of the time and I decided it was time to install it locally. I am running Server 2008 after all with a full blown install of SQL 2008, so I figured adding SharePoint and PerformancePoint would be less overhead and make some of my development a lot easier.
For those of you wanting to install PerformancePoint Monitoring using SQL 2008, reference my post here.
The root of the error is with the System.Web.Extensions reference in the application. Particularly for anyone running Vista, Server 2008 or even Windows 7, the likelihood that you have 3.5 installed and therefore do not use AJAX 1.0 is pretty high.
So, how to resolve? There is the easy way and then the hard way.
Easy way: Download and install ASP.NET AJAX 1.0. This will put the 1.0.61025.0 versioned DLL reference in the GAC for the application to pull out when needed.
Hard way: Post here with details on resolving old reference to the new. While there is a lot I like about this approach, the only fear is that work done might be undone with Service Packs, hot fixes, etc.
Unless there is no other work solution, I generally avoid these situations as clients do not like to have to call me when it breaks after they have updated or installed the latest service pack.
Beyond that, the resolution can be pretty simple. If you are working locally, just remember to update your hosts file!
Tags: performance point,
sql 2008,
wss,
ajax
Categories: Performance Point