January 30, 2014 at 7:21 pm
Happy New Year 4712!
Eat lots of noodles, wear something red.
Practice your 'Gung Hei Fat Choi!'
January 31, 2014 at 12:15 am
Stefan Krzywicki (1/30/2014)
rodjkidd (1/30/2014)
Serious Doh! Moment today.SSIS process has failed this morning. Business noticed before the support guys. Error says a problem with a column - the files uses ; as a delimiter, so I thought maybe there's an extra one there or something wrong with the file.
After a couple of misses with coping the package and importing into SSDT - it's 2008 on the server. I finally got it to run on my test system and it works...
Strange...
Went to talk to one of the Prod DBA's as my access on the server is limited, and he spots that the server has run out of space! Doh!
Nothing wrong with the package or job, other than the error message sent me on a wild goose chase! <facepalm>
Rodders...
SSIS error messages are the worst.
SSAS error messages are way worse.
They don't tell you what is actually going on, the actual error message is in a warning. Go figure.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 31, 2014 at 3:00 am
Greg Edwards-268690 (1/30/2014)
Ed Wagner (1/30/2014)
Jeff Moden (1/30/2014)
rodjkidd (1/30/2014)
Company has out sourced prod DBA team. Been up and running since start of year, so some things are still falling through the cracks. So don't know if they will implement their own monitoring or use the software in place. Depends on who "owns" the servers I guess.That gives me leg cramps just thinking about that. Doing a disk space check should be second only to checking the Recovery Models and the Backups, both of which should be done the first day (or before) of "taking over".
I suspect that "no one" will soon become the owner of the servers.
Of course they will. After all, that's what outsourcing is all about - lack of accountability and plausible deniability.
It depends on if the company is providing the servers and space, but this is pretty basic.
One of the issues with outsourcing can be not having the right people involved in the evaluation process.
Who is responsible for what, and the process to escalate an issue are very important.
Monitoring was one of the first things we mapped out.
We didn't want a surprise.
Nothing is worse than having an issue, most users just go so far as 'it is broken'. It does not matter if it is still your responsibility or not,
'Just fix it'.
I think now is a good time to mention they have two out sourcing companies. One for infrastructure, one for Dev. We are counting down the days to the first occurrence of it was they fault not ours.
So there is a middle band of perms including us contractors in the middle. It will be fun soon.
I have to say the infrastructure guys aren't doing too badly, lots of systems, seem to be on top of most, but of course space issue on server they haven't quite got up to speed on. Doesn't help it seemed to have got forgotten last year as well, so had outages so the business users are a bit jumpy. Why? Well it seems the server is, incorrectly, down as decommissioned but isn't. I think it's because when it was built it was incorrectly named and no one has corrected it. It's internal name suggests it's for a different project. Also the job uses the wrong project name. Only found this out this week. Confused the heck out of the business user as well, when they found out.
So I'm now of the opinion this is really what's at the root. Server assigned to wrong service, and therefore isn't supported correctly. I think I'll mention that, once I've worked out who could do something about it.
Of course if we just called it Bob we'd be fine 😀
But yep , when I was on the Prod side, backups - monitoring, find out about DR, then the rest, was the order of the day when starting somewhere new.
Rodders...
January 31, 2014 at 6:01 am
Double edged sword putting logic in server names.
Get it wrong or have a server that has multiple roles and it doesn't work so well.
Some indication of location (especially for printers) is helpful.
Especially when you have multiple sites.
And desktop, laptop have some use.
It is interesting when you have multiple servers used as a group to server up BI Data.
We had SQL, SSRS, SSAS, SharePoint, Citrix, and a file server.
They wanted to split off just the SQL, and we resisted.
Quite a few moving pieces, and they all had to be working together.
We were also on a different track for upgrades - tending to install newer versions soon after release for the BI features.
January 31, 2014 at 6:19 am
Luis Cazares (1/30/2014)
Would this help?http://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/guid-vs-int-debate.aspx
Maybe even a post from a GUID lover:
http://blogs.msdn.com/b/sqlazure/archive/2010/05/05/10007304.aspx
Or even BOL on Using uniqueidentifier Data:
http://technet.microsoft.com/en-us/library/ms190215(v=sql.105).aspx
I especially like Kimberly's article Luis posted here. Sean, this may be a good first step in illustrating your point. There's the index fragmentation problem and also the space requirements, which Kimberly illustrates well. Another point is that all NCIs will inherit the bytes of the primary key for each row, so you're going to pay the price for space more than once. I got the opportunity to hear Kimberly speak once about keys and she really drove the point home about making sure your primary keys are narrow, unique, never-changing and ever-increasing.
Being a fan of integers myself, I found myself wondering how the two data types would stack up against each other in a very simple scenario. So, I created a couple of 1M row test table and compared a few simple tests. The results are not completely unexpected, given that you're dealing with more bytes per row. This assumes you have an ITVF called TallyN that's an implementation of Itzik's inline tally table.
if OBJECT_ID('test_int', 'u') is not null drop table test_int;
create table test_int (
id integer not null,
constraint test_int_pk primary key (id));
if OBJECT_ID('test_guid', 'u') is not null drop table test_guid;
create table test_guid (
guid uniqueidentifier not null,
constraint test_guid_pk primary key (guid));
set statistics time, io on;
--populate each test table with 1M rows
insert into test_int(id)
select N
from dbo.TallyN(1000000);
--elapsed time = 37347 ms
--data space = 12.563 MB
--index space = 0.055 MB
insert into test_guid(guid)
select NEWID()
from dbo.TallyN(1000000);
--elapsed time = 53550 ms
--data space = 24.188 MB
--index space = 0.172 MB
--do a simple count to compare the reads
select COUNT(id) from test_int;
--esapsed time = 43 ms
--logical reads = 1615
select COUNT(guid) from test_guid;
--esapsed time = 46 ms
--logical reads = 3118
--we know we won't have any, but compare looking for duplicate values
select id, COUNT(*)
from test_int
group by id
having COUNT(id) > 1
order by 1;
--esapsed time = 76 ms
--logical reads = 1615
select guid, COUNT(*)
from test_guid
group by guid
having COUNT(guid) > 1
order by 1;
--elapsed time = 168 ms
--logical reads = 3118
--do a straight-up select of the first 1K rows
select top 1000 id from test_int order by id;
--elapsed time = 0 ms
--logical reads = 5
select top 1000 guid from test_guid order by guid;
--elapsed time = 1 ms
--logical reads = 11
set statistics time, io off;
The additional bytes consumes for each row make us pay a price for using them. The engine has to do more work to move around the bytes when executing each query and disk IO goes up. I know this is not a comprehensive test with child tables and foreign keys to test the reads of a join, but I thought the simplest queries would make the point. I hope this helps, or at least gets you started with creating a more comprehensive set of queries to illustrate the point.
January 31, 2014 at 9:33 am
Ed Wagner (1/31/2014)
Luis Cazares (1/30/2014)
Would this help?http://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/guid-vs-int-debate.aspx
Maybe even a post from a GUID lover:
http://blogs.msdn.com/b/sqlazure/archive/2010/05/05/10007304.aspx
Or even BOL on Using uniqueidentifier Data:
http://technet.microsoft.com/en-us/library/ms190215(v=sql.105).aspx
I especially like Kimberly's article Luis posted here. Sean, this may be a good first step in illustrating your point. There's the index fragmentation problem and also the space requirements, which Kimberly illustrates well. Another point is that all NCIs will inherit the bytes of the primary key for each row, so you're going to pay the price for space more than once. I got the opportunity to hear Kimberly speak once about keys and she really drove the point home about making sure your primary keys are narrow, unique, never-changing and ever-increasing.
Being a fan of integers myself, I found myself wondering how the two data types would stack up against each other in a very simple scenario. So, I created a couple of 1M row test table and compared a few simple tests.
Don't forget that since all those extra bytes of GUID primary keys embedded in non-clustered indexes result in fewer rows per page, it will also require more pages of the buffer cache (i.e., more RAM) to hold the same number of rows compared to a table with exactly the same data but an int or bigint primary key. GUID primary keys are chewing up both I/O and RAM, requiring an even stronger justification for incurring this cost. If I have time today, I'll look at the memory grants requested and granted for Ed's test data and queries - I expect we'll see some difference.
Jason Wolfkill
January 31, 2014 at 9:40 am
wolfkillj (1/31/2014)
Ed Wagner (1/31/2014)
Luis Cazares (1/30/2014)
Would this help?http://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/guid-vs-int-debate.aspx
Maybe even a post from a GUID lover:
http://blogs.msdn.com/b/sqlazure/archive/2010/05/05/10007304.aspx
Or even BOL on Using uniqueidentifier Data:
http://technet.microsoft.com/en-us/library/ms190215(v=sql.105).aspx
I especially like Kimberly's article Luis posted here. Sean, this may be a good first step in illustrating your point. There's the index fragmentation problem and also the space requirements, which Kimberly illustrates well. Another point is that all NCIs will inherit the bytes of the primary key for each row, so you're going to pay the price for space more than once. I got the opportunity to hear Kimberly speak once about keys and she really drove the point home about making sure your primary keys are narrow, unique, never-changing and ever-increasing.
Being a fan of integers myself, I found myself wondering how the two data types would stack up against each other in a very simple scenario. So, I created a couple of 1M row test table and compared a few simple tests.
Don't forget that since all those extra bytes of GUID primary keys embedded in non-clustered indexes result in fewer rows per page, it will also require more pages of the buffer cache (i.e., more RAM) to hold the same number of rows compared to a table with exactly the same data but an int or bigint primary key. GUID primary keys are chewing up both I/O and RAM, requiring an even stronger justification for incurring this cost. If I have time today, I'll look at the memory grants requested and granted for Ed's test data and queries - I expect we'll see some difference.
Thanks all. You have given me some direction to find some more solid evidence as to why this is a bad idea. Kimberly Tripp's article was one I remember reading but couldn't find it yesterday. I knew that using a guid as the clustered key would cause huge fragmentation but need something behind to back it up.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 31, 2014 at 3:28 pm
Quick note: We are always looking for articles and I'm trying to keep a list here. Most are relatively short requests if any of you are looking to write.
If you publish with us, we'll buy you lunch/dinner ($25) and you can also stick the article on your own blog after a couple months.
January 31, 2014 at 3:30 pm
In line with Sean's issue. If a few of you want to write this up in a slightly more formal way to note that it's a bad idea to use uniqueidentifiers, we'd love a couple articles. That might give more straightforward evidence.
Course, Sean, you're welcome to do one as well.
January 31, 2014 at 3:56 pm
Steve Jones - SSC Editor (1/31/2014)
In line with Sean's issue. If a few of you want to write this up in a slightly more formal way to note that it's a bad idea to use uniqueidentifiers, we'd love a couple articles. That might give more straightforward evidence.Course, Sean, you're welcome to do one as well.
I would love to handle this one Steve. I have yet to publish anything here and this seems like a decent choice to get started.
I have spent most of the last 2 days working on this. I even have some interesting information that will contradict some of what many other professionals have stated out there. How long should this article be? I would think that maybe we keep the focus to why it is important to not use uniqueidentifiers as the clustered index. I am afraid that anything more general could easily turn into a 2 chapter project.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 31, 2014 at 4:15 pm
I think this can be tackled in a few ways.
1. Reasons why a unique identifier doens't make a good CI - 2-5 pages. could cover complexity of dealing with data that's in this format.
2. Comparison of int/varchar w/ uniqueID in a design, pros/cons (4-8 pages, depending on examples used)
3. Performance comparison of choices - 4-5 pages, perhaps less, but I'd like 2-3 scenarios
4. Rant (prefer to avoid this as an article, but it might be a nice editorial) - 1 page
5. ??
February 3, 2014 at 3:48 am
Jeff Moden (1/25/2014)
Shifting gears a bit, I've seen a trend starting to develop that will define the next wave of performance and memory problems. Even some people that I think have a good handle on T-SQL are starting to say things like "It doesn't matter because our database fits in memory" or "It doesn't matter because we have SSDs" with "it" being good code.The Bread'n'butter database that we have at work all fits in memory and yet we have some serious performance problems with some of the legacy code because of logical reads. Some recent fixes were to relatively simple code that did (and this is not a typo) 10 to 16 TRILLION reads per 8 hour period. And it wasn't just one piece of code, either. Some of the code was high hit front end code and some of it was batch code. I have to tell you that even memory has a problem with multiple pieces of code that consume 10 to 16 Trillion reads each even when it is spread out over an 8 hour period.
Adding more memory isn't going to fix that kind of code for performance or throughput.
That probably qualifies as one of the more asinine statements I've heard in a while (and I mean the one you're quoting before one of our local wags gets all huffy). I pretty much guarantee you that better coding practices are becoming more important as more and more work is loaded into hosted solutions, cloud or not.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 3, 2014 at 4:37 am
rodjkidd (1/29/2014)
Talking of Grant, did anyone see the picture of him on the SQLCruise yesterday- almost as red as the livery of that company he works for!Rodders...
Unless it was while we were in the hot tub, I wasn't burnt. I used SPF 5TB the whole time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 3, 2014 at 4:40 am
Greg Edwards-268690 (1/29/2014)
GilaMonster (1/29/2014)
Ed Wagner (1/29/2014)
GilaMonster (1/29/2014)
Could use some of that chill down here. We had a couple of weeks of 35 C+ (high for JHB). Seriously unpleasant.That sounds seriously unpleasant - that's 95 F. We're on opposite ends of the spectrum right now.
It's cooled down since. Fortunately.
Houses here don't typically have built in heating or air conditioning, the weather isn't usually extreme enough to need it.
Speaking of weather, Gail, I saw a documentary on strange weather phenomenon a couple days ago and they talked about one that occurs in either Cape Town or Johannesburg. Have you ever seen the mountain where the clouds stay near the top and continuously roll downwards? I think it was called a table cloud. It looked really cool.
Table Mountain, Cape Town.
I have seen over 100 F, and under -30 F here, so it's hard to imagine no heater or air conditioning.
And with Alaska hitting 62 F yesterday, when our high was 0 F, makes me question why do I live here.
I'll have to look for Table Mountain, sounds very interesting.
Speaking of weather and the cruise, at one point, we were over 100 degrees (F) different between the weather we were experiencing and what was going on at Tim's house in Michigan. That's a pretty severe difference.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 3, 2014 at 4:46 am
wolfkillj (1/29/2014)
I created and used my first extended events session last week, and it was great - easy and extremely helpful. I just posted about it on my blog, if you're interested in reading about my experience:
WHOOPP!!! Extended Events.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 42,721 through 42,735 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply