SQL query read or write operation

[xfilos]

Hey guys,

Querying a sql database from management studio is a read or write operation ? am asking this because my SSD shows as write frequency higher than read frequency in task manager while querying a database whic i have installed for home use. Can anyone confirm what it really does while querying ?

Thanks in advance.

 

[jstern01]

Generally its a read operation. But you might have writes if there is a join statement creating temporary work tables. Posting the sql query would help in further explaining what operations are being done.

Jim

 

[Nothinman]

Have you used resource monitor to see which processes are actually doing the writing?

 

[xfilos]

Generally its a read operation. But you might have writes if there is a join statement creating temporary work tables. Posting the sql query would help in further explaining what operations are being done.

Jim

Hi, it is just a select * from single query and the table has around 10 lakh records. They are retrieved pretty fast but iam concerned about the write spikes going on SSD.

Have you used resource monitor to see which processes are actually doing the writing?

Hi , ya i tried it like 5 times in the task manager in windows 8 which shows the graph and read/write speed at the bottom.The write speed spikes pretty much while i execute the query. After the execution it just drops to normal, i tried it multiple times and it does spike while execution of the query. The query execution takes around 23 sec which is the time the write goes like 7mb to 11mb and read stays low.

I will check from resource monitor once and will let you know.

Thanks

 

[Nothinman]

I would look at it more than once, but it lists the I/O happening per-process so it should tell you what's doing the writing so you can determine for sure if it's your query or something else in the background like indexing.

 

[nandhini]

Hi All,
I have a sql query which returns me the start and end time of certain process, i just need another output being displayed beside, which gives me the difference of end time and start time, im able to do that by just subtracting end time minus start time, but the difference returns me null.

i have pasted the query below,
select PROCESS.START_TIME, PROCESS.END_TIME, (PROCESS.END_TIME-PROCESS.START_TIME)As Difference from DOCKETS_PUB.PROCESS

this difference column returns me null, im doing this in a dbvisualizer.


Any help on this would be appreciated.


Thanks,
Nandhini

 

[Nothinman]

Hi All,
I have a sql query which returns me the start and end time of certain process, i just need another output being displayed beside, which gives me the difference of end time and start time, im able to do that by just subtracting end time minus start time, but the difference returns me null.

i have pasted the query below,
select PROCESS.START_TIME, PROCESS.END_TIME, (PROCESS.END_TIME-PROCESS.START_TIME)As Difference from DOCKETS_PUB.PROCESS

this difference column returns me null, im doing this in a dbvisualizer.


Any help on this would be appreciated.


Thanks,
Nandhini

Not only have you hijacked a threat on a separate topic, you hijacked one in the wrong forum. You should remove this post if possible and repost it in the Programming section. Depending on the datatype of the time columns, you should probably be using the DATEDIFF function if it's T-SQL we're talking about here.

 

[nandhini]

Not only have you hijacked a threat on a separate topic, you hijacked one in the wrong forum. You should remove this post if possible and repost it in the Programming section. Depending on the datatype of the time columns, you should probably be using the DATEDIFF function if it's T-SQL we're talking about here.

sorry about that

 

[snoylekim]

What was stated before re joins and internal tables is correct, plus most SQL based DBMS have a plethora of internal tables designed to manage the database environment .. enqueue management often relies on internal updates to temporary tables for lock management ..more common in multiuser environments..
At the Windows level , NTFS is doing writes to the MFT and some other stuff pretty constantly .. there's really no such thing as true 'read only' when looking at a low enough level ..I know some folks were concerned with the whole 'write life' for SSDs , but , as much as one tries to isolate 'read only' activity , a device will be written to by the system and application software ..writes that you (fortunately) have limited control over.

 

[xfilos]

What was stated before re joins and internal tables is correct, plus most SQL based DBMS have a plethora of internal tables designed to manage the database environment .. enqueue management often relies on internal updates to temporary tables for lock management ..more common in multiuser environments..
At the Windows level , NTFS is doing writes to the MFT and some other stuff pretty constantly .. there's really no such thing as true 'read only' when looking at a low enough level ..I know some folks were concerned with the whole 'write life' for SSDs , but , as much as one tries to isolate 'read only' activity , a device will be written to by the system and application software ..writes that you (fortunately) have limited control over.

Ya but will that writes have much higher frequency than read ? in this case it is like 7-11mb write and 1mb to 2mb read for returning 10 lakh records from select query. Just wanted to confirm on that.But i do have a doubt as nothinman said may be some other process is going on. Iam still stuck at office most of the time, have to test the resource monitor scenario with test cases to confirm.

Xfilos.

 

[KentState]

If you are really concerned about wearing out an SSD, then put it on a hard disk. I initially developed an 80GB data warehouse on my work laptop and was able to deal with it. If this database is really that small, then you don't need the performance of an SSD. You really need to look at Permon and the various SQL Server counters to get a better idea. BTW, are you running Express or Standard edition?