Serious performance issues in SQL Server 2008

Get help on databases - MySQL, Oracle, Access, etc.
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Serious performance issues in SQL Server 2008

Post by Tribble »

Mmmm it is a brand new machine. I will get them to check it out.

On another terrifying issue. They have about 200 machines that log into my database - I use Windows Authentication. They have been logging into each machine as the same user. The strange thing is that they have everyone added in active directory. Not sure they understand how it works. Yes - they are all on a domain.
Image
User avatar
hamin_aus
Forum Moderator
Posts: 18363
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: Galax GTX1080
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: Serious performance issues in SQL Server 2008

Post by hamin_aus »

This thread is gold!
Image
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Serious performance issues in SQL Server 2008

Post by Tribble »

I can hear your laughter all the way over here Jammy and I don't blame you. But this is what I have to deal with - daily.

And remember that I am clueless myself. I am used to hosting my databases on servers that are wonderfully managed by Admins who know what they are doing. And they generally give me a lot of assistance when it comes to the technical stuff.

Remember when the database was "too big" and I had to remove data? Well I saw an email last night asking me to put all the data back because one of the agents has defrauded a whole lot of people and they need to see how many she had access to. Yes she has been arrested - she was part of a syndicate. She sold 385 in her time with them. Now we have to work out how many were fraudulent. :lol: And the worst of it - she knows she will be out soon. How do such young people know so much about being dishonest?
Image
Anakha56
Forum Administrator
Posts: 22136
Joined: 14 Jun 2004, 02:00
Processor: Ryzen 1700K
Motherboard: Asus X370
Graphics card: Asus 1060 Strix
Memory: 16GB RAM
Location: Where Google says

Re: Serious performance issues in SQL Server 2008

Post by Anakha56 »

So... Could I be wrong? :oops: To me it would seem to be RAM since everything is going corrupt but I know nothing about SQL, Access and the whole database stuff...
JUSTICE, n A commodity which is a more or less adulterated condition the State sells to the citizen as a reward for his allegiance, taxes and personal service.
User avatar
hamin_aus
Forum Moderator
Posts: 18363
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: Galax GTX1080
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: Serious performance issues in SQL Server 2008

Post by hamin_aus »

Anakha56 wrote:I know nothing about SQL, Access and the whole database stuff...
Ordinarily I'd say that using the phrase "the whole database stuff" immediately disqualifies your opinion and also takes you off the waiting list for a lobotomy because you've clearly already had one..

But in this case I'd say you were the ideal person to hazard a guess at whats going on :P
Image
Anakha56
Forum Administrator
Posts: 22136
Joined: 14 Jun 2004, 02:00
Processor: Ryzen 1700K
Motherboard: Asus X370
Graphics card: Asus 1060 Strix
Memory: 16GB RAM
Location: Where Google says

Re: Serious performance issues in SQL Server 2008

Post by Anakha56 »

ROFL. Your confidence in me is awe-inspiring :P.
JUSTICE, n A commodity which is a more or less adulterated condition the State sells to the citizen as a reward for his allegiance, taxes and personal service.
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Serious performance issues in SQL Server 2008

Post by Tribble »

Well good news people. Day one of the course has told me that I am normalizing to the 3rd level. I believe that to be acceptable - if not ideal.
I am also using the correct data types and tomorrow I find if I am indexing correctly.

This is a lot of fun. Writing scripts to do what I would do in the GUI - very nifty.

<yes Jamin - you may laugh> :lol:
Image
User avatar
hamin_aus
Forum Moderator
Posts: 18363
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: Galax GTX1080
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: Serious performance issues in SQL Server 2008

Post by hamin_aus »

Tribble wrote:Day one of the course has told me that I am normalizing to the 3rd level.
From past descriptions of your data model you cant be at 3rd normal form yet. Not with one honking big table...
Tribble wrote:Writing scripts to do what I would do in the GUI - very nifty.

<yes Jamin - you may laugh> :lol:
I don't think someone learning the correct way to do things is a laughing matter.
It's much funnier when they plod along doing the wrong thing and wondering why it never works properly.

By learning SQL Server best practises you are actually making this LESS amusing :cry:
Image
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Serious performance issues in SQL Server 2008

Post by Tribble »

Bwaahahahaha don't worry - I will make mistakes yet. I am sure you will find amusement for many years yet.

And I think you misunderstood. I don't only have one table :P Yes one it bigger than I would like it to be - but that is because I am useless at scripting and need to pass all that data back into a text file. And the best way I could do it was to keep it all together. Now that I am "learning" I should be able to do the same with a query or view and then I can split that huge table into smaller ones.

And yes - I use an Access query to populate the table from the huge text file the bank sends me. Hopefully I will learn to do that in SQL too.
Image
User avatar
hamin_aus
Forum Moderator
Posts: 18363
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: Galax GTX1080
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: Serious performance issues in SQL Server 2008

Post by hamin_aus »

Many ways to populate a SQL table from a text file that are more economical than *spits* Access.

Assuming a comma separated text file, play with this:

Code: Select all

CREATE TABLE #TempTable (TempColumn1 varchar(20), TempColumn2 varchar(20)) 
BULK INSERT #TempTable
FROM 'c:\test-file.txt' 
WITH 
( 
FIELDTERMINATOR = ',', 
ROWTERMINATOR = '\n' 
) 
GO 

INSERT INTO dbo.LiveTable (LiveColumn1, LiveColumn2) 
SELECT TempColumn1, TempColumn2
FROM   #TempTable
FIELDTERMINATOR is the character they use to separate data (comma, tab, pipe etc)
In case you haven't covered it, #Tables are temporary tables and only live as long as the SPID that created them does. Handy for creating on-the-fly tables to storing temporary data


Hopefully they will cover SQL Server Integration Services (SSIS) in your course.
If the bank always sends you text data with the same formatting and column order you can use that to create an import job.
Image
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Serious performance issues in SQL Server 2008

Post by Tribble »

SSIS is a definite on this course. And we also have learnt about ##table :D So I am getting there.

Thanks for the code - I will definitely play with it but my text files have no delimiters. Solid text strings. I do have each field's character position though. So I assume I would need to use a find command to locate the correct characters.
Image
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Serious performance issues in SQL Server 2008

Post by Tribble »

Oh and I am learning 2012
Image
User avatar
hamin_aus
Forum Moderator
Posts: 18363
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: Galax GTX1080
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: Serious performance issues in SQL Server 2008

Post by hamin_aus »

Tribble wrote:Solid text strings.
Ugh.
Tribble wrote:Oh and I am learning 2012
Well of course, what else would you learn :D
Image
Anakha56
Forum Administrator
Posts: 22136
Joined: 14 Jun 2004, 02:00
Processor: Ryzen 1700K
Motherboard: Asus X370
Graphics card: Asus 1060 Strix
Memory: 16GB RAM
Location: Where Google says

Re: Serious performance issues in SQL Server 2008

Post by Anakha56 »

jamin_za wrote:
Tribble wrote:Oh and I am learning 2012
Well of course, what else would you learn :D

MySQL, Oracle... :P
JUSTICE, n A commodity which is a more or less adulterated condition the State sells to the citizen as a reward for his allegiance, taxes and personal service.
User avatar
hamin_aus
Forum Moderator
Posts: 18363
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: Galax GTX1080
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: Serious performance issues in SQL Server 2008

Post by hamin_aus »

Awwww, baby's first troll...
Image
Anakha56
Forum Administrator
Posts: 22136
Joined: 14 Jun 2004, 02:00
Processor: Ryzen 1700K
Motherboard: Asus X370
Graphics card: Asus 1060 Strix
Memory: 16GB RAM
Location: Where Google says

Re: Serious performance issues in SQL Server 2008

Post by Anakha56 »

Yeah I can never beat the ninja troll though... :twisted:
JUSTICE, n A commodity which is a more or less adulterated condition the State sells to the citizen as a reward for his allegiance, taxes and personal service.
User avatar
Tribble
Registered User
Posts: 88465
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: Serious performance issues in SQL Server 2008

Post by Tribble »

We use MySQL with our radio station software but I have nothing to do with those programs. As I train MS products - I stick to MS SQL. And I must say - I am loving it. Powerful!

Today was a lot more technical. We were dealing primarily with indexing and joins. I never want to meet an anti semi Left outer join!!!!! The name alone scares me :shock: Also learnt to see how fragmented my indexes are - so will be logging in and seeing how bad things are. Then I will be dropping them and rebuilding :D I doubt that I will ever be able to train this course - but I can tell you I will soon be rewriting this database.
Image
Post Reply