March 30, 2010 at 8:03 am
Hi to all,
I have a databse table with a primary key Testkey as varchar(8) and one status flag (int).
The primary key contains a code with numbers and characters.
The database has 250 Million rows and the query response time is very worse.
Is ther anybody who can give some helpfull tips to me to optimize the table.
Regards
March 30, 2010 at 8:59 am
Understand the select query requirement and think of following options-
1. Partitioning the table
2. Identify and Adding non clustered indexes
3. Checking the query plan to see indexes are being used properly or any kind of query re-structuring may help.
4. Check indexes are not fragmented
5. Statistics are updated properly
6. Check to avoid any kind of blocking
March 30, 2010 at 10:32 am
Code and structure are your primary points of pain when it comes to performance. Your structure needs to be laid out such that the code is facilitated with a good clustered index and, as needed, a small number of non-clustered indexes to augment the cluster. The code has to be written in such a way that it takes advantages of the structure that you've built. That's about it in a nutshell.
To give you more specifics would require more specific information. What does the entire structure look like? What is the data distribution (how many unique values are there) across your indexes?what's it's selectivity is another way to phrase it. What do the queries look like? What do the execution plans for the queries within your environment look like?
With that kind of information, we can begin to suggest specific areas to work on to improve performance. Without that information, it's just a guessing game.
"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
March 30, 2010 at 11:53 pm
Hi,
thank you for the kind reply. Here are some additional information:
The table structure:
CREATE TABLE [dbo].[Code](
[Code] [varchar](8) NOT NULL,
[State] [tinyint] NOT NULL,
CONSTRAINT [PK_Code] PRIMARY KEY CLUSTERED
(
[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
We have a stored procedure who validate a code against the code coulumn and update the state field after the check.
I do not design the table but now I have the responsibility and therefore the pain.. Another pain is the creation of new codes
we have a stored procedure which checks if a code exists and if not it will be inserted. This stored procedure runs very long
and we need every month 10 Million new codes, therefore this is another bottleneck.
Thank's for every help.
March 31, 2010 at 4:44 am
Part way there. That's the structure, but what do the queries look like? And what do the execution plans look like? Can you run this query against the table and tell me what your statistics look like on the table:
DBCC SHOW_STATISTICS('Code','PK_Code')
Also, you need to know the fragmentation of your index:
SELECT s.avg_fragmentation_in_percent
,s.fragment_count
,s.page_count
,s.avg_page_space_used_in_percent
,s.record_count
,s.avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID('yourdatabase'),OBJECT_ID(N'Code'),NULL,NULL,'Sampled') AS s
The query, the execution plan, the statistics and the fragmentation provide the information needed along with the structure to figure out why a query is running slow.
"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
April 1, 2010 at 5:07 am
Hi,
can you send a suggestions for a design of one table with 250 millions rows ?
Greetings
April 1, 2010 at 5:35 am
scziege (4/1/2010)
Hi,can you send a suggestions for a design of one table with 250 millions rows ?
Greetings
I'm sorry, but no. Not without the business requirements, what's available for the physical plant, the size of the columns... It's just not possible.
The number of rows is not the issue 250 million rows that only contain, let's say, 3 int columns, can be very easily handled by a relatively modest system and a decent clustered index. Take the same 250 million rows and add six or eight columns that include mixed data including varchar... and the world changes. You probably need to start looking at physical partitioning in order to distribute the storage and retrieval. That means you need to identify a good mechanism for partitioning the data and build out the infrastructure to support that.
In short, there's no one right answer and the number of rows is just not the only determinant to pick a good answer.
A free online forum like this is great for getting some general advice or, even better, for answering very specific technical issues. It's not the place to go to get free consulting. If you want me to design a major system for you in my spare time, I'm more than willing to discuss my rate and availability, but you can't honestly expect that kind of thing for free.
Here's what I'll tell you. Look at your queries. Look at the execution plans for the queries. Try to understand what they're telling you that SQL Server is doing with your data. They will suggest the likely bottlenecks. Look at your system, specifically at the wait states and queues. That will tell you where the server is bogging down. With that information you can start making determinations of where to go with your system. When you have specific, targeted, technical questions, come on back to SQL Server Central and post them. There are great people here, who know a lot more than I do, who can help.
"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
April 1, 2010 at 6:26 am
scziege (4/1/2010)
Hi,can you send a suggestions for a design of one table with 250 millions rows ?
Greetings
Absolutely...
Step 1: Define the purpose of the of table and the underlying data according to the business requirements.
Step 1: Study indexing and index maintenance until you puke.
Step 1: Study the advantages/disadvantages of partitioned views and partitioned tables.
Step 1: Design the table with normalization in mind. That may actually mean splitting the table into logical non-repeatable components. When you think you have it properly normalized, normalize some more.
The hard part about all of the above is that all of the steps must be taken at the same time which is why they're all numbered "Step 1"
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2010 at 10:17 am
1) maybe response time is poor because your server is weak or poorly configured. how much RAM? how many CPUs?
2) what query or queries are you running against the table that is slow?
3) Have you done a file IO stall analysis or wait stats analysis?
4) for the PK field, varchar is inefficient. What is the average length of the field? If it is anywhere near 8, then change to a char(8) and pick up some storage and perf gains.
5) If you check for a status that is very specific (like 1, where 99.x% of the rows are status 0) then you can actually gain from an index on the status column.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 1, 2010 at 1:22 pm
scziege (4/1/2010)
Hi,can you send a suggestions for a design of one table with 250 millions rows ?
Greetings
Design depends on needs, requirements, queries, etc.
Can you provide any of that?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2010 at 4:32 am
scziege (3/30/2010)
...Another pain is the creation of new codes
we have a stored procedure which checks if a code exists and if not it will be inserted. ...
I truly hope the new codes will be generated in ascending order and not being random codes. Otherwise you may end up with heavy page splits due to your clustered index.
What is the specific reason for creating that many codes using a stored procedure instead of simply using a numeric(12,0) data type, for instance?
There are many downsides in creating rule-based character code values, especially if those are used as a clustered index and are not ascending by definition.
Another issue you need to be aware of is to check against a "black list" of invalid codes due to offending/illegal context (e.g. "a..h0.e" or "Terorist"). To have the code "RedSocks" might not be that amusing as well unless you're a fan...
If the requirement would allow it I most probably would have used a numeric column as clustered index and a UID column to hold the code. But it's guessing from that point on...
April 2, 2010 at 6:05 am
Using a number does make sense, but I wouldn't suggest you use NUMERIC. The NUMERIC(12,0) suggested is 9 bytes in size. An INT is only 4 bytes and covers 10 digits. If you're dealing with truly huge amounts of data you can go to BIGINT which is still only 8 bytes and coveres 19 digits.
"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
April 2, 2010 at 7:06 am
Grant Fritchey (4/2/2010)
Using a number does make sense, but I wouldn't suggest you use NUMERIC. The NUMERIC(12,0) suggested is 9 bytes in size. An INT is only 4 bytes and covers 10 digits. If you're dealing with truly huge amounts of data you can go to BIGINT which is still only 8 bytes and coveres 19 digits.
My assumption that INT would not be sufficient is based on CHAR(8) and 36 possible values for each position [0..9] and [a..z] which is someting like 2.8 E12. Therefore I used a 12 digit number. But you're right, BIGINT would be the better choice here.
April 2, 2010 at 7:54 am
I don't understand this talk about numbers since the OP clearly stated "... a code with numbers and characters". Can't put those characters in an int, bigint, numeric, decimal, etc. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 2, 2010 at 8:00 am
TheSQLGuru (4/2/2010)
I don't understand this talk about numbers since the OP clearly stated "... a code with numbers and characters". Can't put those characters in an int, bigint, numeric, decimal, etc. 🙂
No, you're wrong... 😛
But seriously, I was thinking, and I'm assuming Lutz was thinking, adding a number or or substituting a number for the char field. Idle chatter really.
"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 - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply