Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««4,2714,2724,2734,2744,275»»»

Are the posted questions getting worse? Expand / Collapse
Author
Message
Posted Thursday, January 30, 2014 7:21 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:50 AM
Points: 4,425, Visits: 3,417
Happy New Year 4712!

Eat lots of noodles, wear something red.

Practice your 'Gung Hei Fat Choi!'
Post #1536616
Posted Friday, January 31, 2014 12:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 13,644, Visits: 10,538
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1536652
Posted Friday, January 31, 2014 3:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, August 30, 2014 7:06 AM
Points: 2,987, Visits: 5,425
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...



Post #1536682
Posted Friday, January 31, 2014 6:01 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:50 PM
Points: 1,232, Visits: 6,646
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.
Post #1536735
Posted Friday, January 31, 2014 6:19 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 4,238, Visits: 3,258

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.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1536747
Posted Friday, January 31, 2014 9:33 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:05 AM
Points: 1,222, Visits: 2,545
Ed Wagner (1/31/2014)

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
Blog: SQLSouth
Twitter: @SQLSouth
Post #1536856
Posted Friday, January 31, 2014 9:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:41 PM
Points: 13,315, Visits: 12,182
wolfkillj (1/31/2014)
Ed Wagner (1/31/2014)

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1536864
Posted Friday, January 31, 2014 3:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:57 PM
Points: 33,206, Visits: 15,361
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.

http://www.sqlservercentral.com/Forums/Forum2824-1.aspx







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1537005
Posted Friday, January 31, 2014 3:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:57 PM
Points: 33,206, Visits: 15,361
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1537006
Posted Friday, January 31, 2014 3:56 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:41 PM
Points: 13,315, Visits: 12,182
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1537017
« Prev Topic | Next Topic »

Add to briefcase «««4,2714,2724,2734,2744,275»»»

Permissions Expand / Collapse