How to Manage Large Tables in sql server 2012

Hi All,

I've table processed raw data where every minute 5 lac data received from listener or processor.

now we get performance problem. how to manage it. can any one have idea.


September 4th, 2015 10:48am

That is not really a very large table. 

What exactly is your issue?  What version of SQL Server are you using?

Free Windows Admin Tool Kit Click here and download it now
September 4th, 2015 11:55am

sql server 2012

05 lac data in munute .

i've issues regarding the performance. cpu spikes high. while fetching the data it's take long time.

September 4th, 2015 12:04pm

sql server 2012

05 lac data in munute .

i've issues regarding the performance. cpu spikes high. while fetching the data it's take long time.

How many rows currently in the table?  Are rows inserted individually, or using a bulk insert technique?  Do you observe blocking?  If you are inserting 500,000 rows per minute, I would expect the table to quickly become large.  Large tables require attention to detail as well as appropriately sized hardware to provide acceptable performance.

Take a look at the execution plans of the problem query to ensure indexes are used as expected.  You may need to update statistics and/or add new indexes to optimize performance.  If you need help with that, post the CREATE TABLE DDL, including indexes and constraints, along with the problem query.

Free Windows Admin Tool Kit Click here and download it now
September 4th, 2015 12:17pm

table------------

CREATE TABLE [dbo].[RawDataAFM7878](
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[stringstatus] [char](10) NULL,
[StringText] [nvarchar](max) NULL,
[ReceivedDateTime] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

----Cluster INDEX---------

CREATE CLUSTERED INDEX [ClusteredIndex-20150904-130857] ON [dbo].[RawDataAFM7878]
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

----Non Cluster Index---

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150904-130915] ON [dbo].[RawDataAFM7878]
(
[stringstatus] ASC,
[ReceivedDateTime] ASC
)WIT

Now i'm doing

September 4th, 2015 12:37pm

Thanks for the DDL.  It looks like the text of your response was truncated.  Please add the answers to the following questions:

  1. How many rows currently in the table? 
  2. How many distinct values for stringstatus in the table? 
  3. Is stringstatus updated after insertion?
  4. Can you share a problem query example? 
  5. What Edition of SQL Server 2012 are you using?

Free Windows Admin Tool Kit Click here and download it now
September 5th, 2015 9:03am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics